Solved

t-sql max date of table

Posted on 2014-11-17
4
126 Views
Last Modified: 2014-11-18
i'm using sql server 2008.

I have a table that looks like this:

http://sqlfiddle.com/#!3/6e117/2

How would I write a query that looks at the EVENTDATETIME column and returns the most recent concert for each artist.

So the result set would show only the most recent concert for each artist.
The result set would look something like this:

desired result set
0
Comment
Question by:maqskywalker
  • 2
4 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
Comment Utility
That would be a simple GROUP BY on artist, and MAX date.
SELECT Artist, Max(EventDateTime) as most_recent_event_dt
FROM TestTable
GROUP BY Artist

Open in new window

If you'd like some more reading on GROUP BY I have an image and code-heavy tutorial out there called SQL Server GROUP BY Solutions
0
 
LVL 1

Author Comment

by:maqskywalker
Comment Utility
Jim but what if I want to show every column like on the screen shot I posted above?
0
 
LVL 9

Accepted Solution

by:
sarabhai earned 400 total points
Comment Utility
SELECT * FROM TestTable WHERE EventDateTime IN ( SELECT  MAX(EventDateTime)  FROM TestTable GROUP BY artist )
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

772 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

14 Experts available now in Live!

Get 1:1 Help Now