How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask ITguy25 Your Own Question
ITguy25
ITguy25, Database Administrator
Category: Programming
Satisfied Customers: 324
Experience:  10+ years computer experience. 5 year in IT Management. I manage multiple servers in a variety of OS
25315603
Type Your Programming Question Here...
ITguy25 is online now
A new question is answered every 9 seconds

Ive got a table with the following columns: UserId, TaskName,

This answer was rated:

I've got a table with the following columns:
UserId, TaskName, FirstName, Lastname, email, ValueDate, Analysis

Users will do tasks over and over again, I just need to pull thier last task they did based on ValueDate.

For example, here are the rows in my table:

UserId TaskName FirstName LastName email ValueDate Analysis
47FFC2D3-A4EF-4296-AA57-00236E967150 clean Bill Hippo [email protected] 2012-02-02 Good
4 7FFC2D3-A4EF-4296-AA57-00236E967150 dry Bill Hippo [email protected] 2012-10-03 bad
47FFC2D3-A4EF-4296-AA57-00236E967150 weigh Bill Hippo [email protected] 2012-02-02 Good
47FFC2D3-A4EF-4296-AA57-00236E967150 weigh Bill Hippo [email protected] 2012-05-12 bad
47FFC2D3-A4EF-4296-AA57-00236E967150 weigh Bill Hippo [email protected] 2012-01-12 Good
EAF6A2C0-A9E1-40FC-AA63-0027BEE69212 clean Mickey Mouse [email protected] 2012-01-04 Good
EAF6A2C0-A9E1-40FC-AA63-0027BEE69212 dry Mickey Mouse [email protected] 2013-01-01 Good
EAF6A2C0-A9E1- 40FC-AA63-0027BEE69212 clean Mickey Mouse [email protected] 2013-02-02 Best
EAF6A2C0-A9E1-40FC-AA63-0027BEE69212 weigh Mickey Mouse [email protected] 2011-02-02 Good

I want a query that will return this:

UserId TaskName FirstName LastName email ValueDate Analysis
47FFC2D3-A4EF-4296-AA57-00236E967150 clean Bill Hippo [email protected] 2012-02-02 Good
47FFC2D3-A4EF-4296-AA57-00236E967150 dry Bill Hippo [email protected] 2012-10-03 bad
47FFC2D3-A4EF-4296-AA57-00236E967150 weigh Bill Hippo [email protected] 2 012-05-12 bad
EAF6A2C0-A9E1-40FC-AA63-0027BEE69212 dry Mickey Mouse [email protected] 2013-01-01 Good
EAF6A2C0-A9E1-40FC-AA63-0027BEE69212 clean Mickey Mouse [email protected] 2013-02-02 Best
EAF6A2C0-A9E1-40FC-AA63-0027BEE69212 weigh Mickey Mouse [email protected] 2011-02-02 Good


Thanks

ITguy25 :

Hello,

ITguy25 :

If you are using MS Access, you can use the LAST function/total method.
You would want the last of each field and group by whichever field you want unique, such as the userid. You can apply sort ascending/descending to the ValueDate field to ensure the result is accurate.



If you are using SQL server commands, you can use TOP. I.e.:
SELECT TOP 1 ValueDate FROM table_name
ORDER BY ValueDate DESC;


Let me know if that works for you!

ITguy25 :

You appear to be offline, so if you would like to reply, please reply back and I will assist. Thanks!

Customer: replied 3 years ago.

I'm using SQL and the table has 1000's of rows. I need to return the most recent row for each task and person. SELECT TOP 1 won't do many any good as it will only return 1 row.

TOP 1 would just issue select for each occurrence.

You could also try something like this:
SELECT UserId, TaskName, FirstName, Lastname, email, ValueDate, Analysis
SELECT customer_nbr, loan_nbr, loan_type, loan_amount, loan_date
FROM (SELECT UserId, TaskName, FirstName, Lastname,
email, ValueDate, Analysis,
ROW_NUMBER() OVER(PARTITION BY UserID
ORDER BY ValueDate DESC) AS rk
FROM TABLENAME) AS L
WHERE rk = 1;
Customer: replied 3 years ago.

That gets me almost there. It's only pulling the last row of any task for each user. I need it to pull the last row for each task per user. make sense?

Sorry, I misunderstood. Let me know if this works for you:

SELECT UserId, TaskName, FirstName, Lastname, email, ValueDate, Analysis
SELECT customer_nbr, loan_nbr, loan_type, loan_amount, loan_date
FROM (SELECT UserId, TaskName, FirstName, Lastname,
email, ValueDate, Analysis,
ROW_NUMBER() OVER(PARTITION BY UserID
ORDER BY ValueDate DESC) AS rk
FROM TABLENAME) AS L
WHERE rk = 1
GROUP BY TaskName;
Customer: replied 3 years ago.

Column 'L.UserId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


 


Adding them to my GROUP BY clause gives me the same thing as earlier.

This should do the trick for you:

SELECT UserId, TaskName, FirstName, LastName, email, ValueDate, Analysis
FROM (SELECT UserId, TaskName, FirstName, LastName,
email, ValueDate, Analysis,
ROW_NUMBER() OVER(PARTITION BY UserID, TaskName
ORDER BY ValueDate DESC) AS rk
FROM TABLENAME) AS L
WHERE rk = 1

Sorry for the trouble. Let me know if all is working now!
ITguy25 and other Programming Specialists are ready to help you

Related Programming Questions