• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

t-sql max date of table

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
maqskywalker
Asked:
maqskywalker
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
maqskywalkerAuthor Commented:
Jim but what if I want to show every column like on the screen shot I posted above?
0
 
sarabhaiCommented:
SELECT * FROM TestTable WHERE EventDateTime IN ( SELECT  MAX(EventDateTime)  FROM TestTable GROUP BY artist )
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now