CODEWITHSUNDEEP
sqlsql-server
RahdixC9
RahdixC9

Reputation: 3

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 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.

Upvotes: 0

Views: 209

Answers (2)

hgulyan
hgulyan

Reputation: 8239

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.

Upvotes: 2

Andomar
Andomar

Reputation: 238048

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.

Upvotes: 1

Related Questions