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 ca开发者_如何学Cn I select so I can get the result like this:

BARCODE      | PRICE  | STARTDATE
007023819815 | 319000 | 2009-02-01 00:00:00.000

select by using max date.

Thanks in advance.

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.

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.