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,

Resolved Question:

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 XXX@XXXXXX.XXX 2012-02-02 Good
4 7FFC2D3-A4EF-4296-AA57-00236E967150 dry Bill Hippo XXX@XXXXXX.XXX 2012-10-03 bad
47FFC2D3-A4EF-4296-AA57-00236E967150 weigh Bill Hippo XXX@XXXXXX.XXX 2012-02-02 Good
47FFC2D3-A4EF-4296-AA57-00236E967150 weigh Bill Hippo XXX@XXXXXX.XXX 2012-05-12 bad
47FFC2D3-A4EF-4296-AA57-00236E967150 weigh Bill Hippo XXX@XXXXXX.XXX 2012-01-12 Good
EAF6A2C0-A9E1-40FC-AA63-0027BEE69212 clean Mickey Mouse XXX@XXXXXX.XXX 2012-01-04 Good
EAF6A2C0-A9E1-40FC-AA63-0027BEE69212 dry Mickey Mouse XXX@XXXXXX.XXX 2013-01-01 Good
EAF6A2C0-A9E1- 40FC-AA63-0027BEE69212 clean Mickey Mouse XXX@XXXXXX.XXX 2013-02-02 Best
EAF6A2C0-A9E1-40FC-AA63-0027BEE69212 weigh Mickey Mouse XXX@XXXXXX.XXX 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 XXX@XXXXXX.XXX 2012-02-02 Good
47FFC2D3-A4EF-4296-AA57-00236E967150 dry Bill Hippo XXX@XXXXXX.XXX 2012-10-03 bad
47FFC2D3-A4EF-4296-AA57-00236E967150 weigh Bill Hippo XXX@XXXXXX.XXX 2 012-05-12 bad
EAF6A2C0-A9E1-40FC-AA63-0027BEE69212 dry Mickey Mouse XXX@XXXXXX.XXX 2013-01-01 Good
EAF6A2C0-A9E1-40FC-AA63-0027BEE69212 clean Mickey Mouse XXX@XXXXXX.XXX 2013-02-02 Best
EAF6A2C0-A9E1-40FC-AA63-0027BEE69212 weigh Mickey Mouse XXX@XXXXXX.XXX 2011-02-02 Good


Thanks
Submitted: 1 year ago.
Category: Programming
Expert:  ITguy25 replied 1 year ago.

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!

Expert:  ITguy25 replied 1 year ago.
Our chat has ended, but you can still continue to ask me questions here until you are satisfied with your answer. Come back to this page to view our conversation and any other new information.

What happens now?

If you haven’t already done so, please rate your answer above. Or, you can reply to me using the box below.
Customer: replied 1 year 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.

Expert:  ITguy25 replied 1 year ago.
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 1 year 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?

Expert:  ITguy25 replied 1 year ago.
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 1 year 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.

Expert:  ITguy25 replied 1 year ago.
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, 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
ITguy25 and 3 other Programming Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7463
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
< Last | Next >
  • http://ww2.justanswer.com/uploads/SP/spatlanta2010/2011-6-23_12450_photo.64x64.gif ATLPROG's Avatar

    ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7463
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
  • http://ww2.justanswer.com/uploads/ComputersGuru/2010-02-13_051118_Photo41.JPG LogicPro's Avatar

    LogicPro

    Computer Software Engineer

    Satisfied Customers:

    5603
    Expert in C, C++, Java, DOT NET, Python, HTML, Javascript, Design.
  • http://ww2.justanswer.com/uploads/unvadim/2010-11-15_210218_avatar.jpg unvadim's Avatar

    unvadim

    Computer Software Engineer

    Satisfied Customers:

    1158
    Good knowledge of OOP principles. 3+ years of programming experience with Java and C++. Sun Certified Java Programmer 5.0.
  • http://ww2.justanswer.com/uploads/lifesaver333/2010-10-17_191349_ls.jpeg lifesaver's Avatar

    lifesaver

    Computer Software Engineer

    Satisfied Customers:

    950
    Several years of intensive programming and application development experience in various platforms.
  • http://ww2.justanswer.com/uploads/EH/ehabtutor/2012-8-2_202016_1.64x64.jpg ehabtutor's Avatar

    ehabtutor

    Computer Software Engineer

    Satisfied Customers:

    864
    Bachelor of computer science, 5+ years experience in software development, software company owner
  • http://ww2.justanswer.com/uploads/RA/rajivsharma086/2012-6-6_17128_displaypic.64x64.jpg Raj's Avatar

    Raj

    Computer Engg.

    Satisfied Customers:

    860
    BE CS, 4+ Experience in Programming and Database (ERP)
  • http://ww2.justanswer.com/uploads/eljonis/2010-01-06_130406_eljon2.jpg Eljon's Avatar

    Eljon

    Consultant

    Satisfied Customers:

    590
    11 yrs of programming (PHP, WordPress, XSL, SQL, JavaScript)
 
 
 

Related Programming Questions