CODEWITHSUNDEEP
sqlsql-serverdistinctgreatest-n-per-group
Nick G
Nick G

Reputation: 1229

SQL Select Distinct column and latest date

I'm looking to select just the latest records of a table based on date, but only one one Distinct listing of each of the urls. The table structure is like this;

ID        URL          DateVisited
1         google.com   01-01-2016
2         yahoo.com    01-02-2016
3         google.com   12-30-2015
4         google.com   02-01-2016

So for my result set I would want

google.com     02-01-2016
yahoo.com      01-02-2016

I will have a couple more conditionals in my actual query, but just want to get the single latest records in a hit log, rather than list of distinct urls and dates, just distinct url's and the latest date.

Upvotes: 19

Views: 43757

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

This is usually done using row_number():

select t.*
from (select t.*,
             row_number() over (partition by url order by datevisited desc) as seqnum
      from t
     ) t
where seqnum = 1;

This allows you to get all the columns associated with the latest record.

Upvotes: 10

Chitharanjan Das
Chitharanjan Das

Reputation: 1343

This is actually pretty easy to do using simple aggregation, like so:

select URL, max(DateVisited)
from <table>
group by URL

Upvotes: 35

Related Questions