Jeremy Kern Jeremy Kern - 4 months ago 14
SQL Question

select Newest Instead of Distinct

Currently I have a stored proc that returns data from a database table that is populated from an API. The current query is:

Select * From Inventory
where StockNumber in
(select distinct stocknumber)
order by Make


Instead of this I actually need to take the top 1 newest record based on a DateTimeStamp Column as there are often many records with the same stocknumber but different timestamp.

Answer

One method is to use a correlated subquery:

Select i.*
From Inventory i
where i.datetime in (select max(i2.datetime)
                     from Inventory i2
                     where i2.stocknumber = i.stocknumber
                    )
order by i.Make;

Another method is to use the ANSI standard window function row_number():

select i.*
from (select i.*,
             row_number() over (partition by stocknumber order by datetime desc) as seqnum
      from Inventory i
     ) i
where seqnum = 1;