Max (SQL-Server)

I have a table that looks like this: BARCODE | PRICE | STARTDATE 007023819815 | 159000 | 2008-11-17 00:00:00.000 007023819815 | 319000 | 2009-02-01 00:00:00.000 How can I se

Solution 1:

SELECT TOP 1 barcode, price, startdate
FROM TableName
ORDER BY startdate DESC

Or if there can be more than one rows.

SELECT barcode, price, startdate
FROM TableName A
WHERE startdate = (SELECT max(startdate) FROM TableName B WHERE B.barcode = A.barcode)

UPDATE changed second query to view max values per barcode.

Solution 2:

An elegant way to do that is using the analytic function row_number:

SELECT  barcode, price, startdate
FROM    (
        SELECT  *
        ,  ROW_NUMBER() OVER (PARTITION BY barcode ORDER BY startdate DESC) as rn
        FROM    YourTable
        ) subquery
WHERE   rn = 1

If performance is an issue, check out some more complex options in this blog post.