Solved

SQL query for this scenario

Posted on 2013-11-28
4
169 Views
Last Modified: 2013-12-01
Hi guys,

Im sure the following can be done in a single query:

I have a table with usernames and dates when they were last active. I need a query to collect the username and the last date they were online. Each username can appear more than once and they will have various dates, ie:

Username            Date

John Smith            01/01/12
John Smith            03/11/12
John Smith            07/12/12
Simon Jones          11/06/13
Simon Jones          15/02/13
Simon Jones          18/09/13

Cheers,
Dean
0
Comment
Question by:deanlee17
  • 3
4 Comments
 
LVL 42

Accepted Solution

by:
Rob Jurd, EE MVE earned 500 total points
ID: 39683071
SELECT  `Username` , MAX(  `Date` )
FROM  `table_logins`
GROUP BY  `Username`
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39683073
That's assuming that the "Date" column is a date field.  If it is stored as text then you'll need to convert it to a date.  In MySQL for example you use str_to_date()
0
 

Author Comment

by:deanlee17
ID: 39683188
Yes it is in date field.

Perfect thanks.
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39683202
Great - let me know if you have any more issues :)
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Left Join with Tuple returning more rows 10 90
SQL query bug 3 28
subtract 1 in Access 2003 query 7 39
Linking a DMV to a database id/sql text in SQL server 2008 8 48
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now