Solved

SQL query for this scenario

Posted on 2013-11-28
4
168 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

12 Experts available now in Live!

Get 1:1 Help Now