tags:

views:

93

answers:

2

Hello everyone.

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.

+1  A: 

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.

Andomar
I think, you are missing `DESC` next to `ORDER BY startdate`
shahkalpesh
@shahkalpesh: Thanks, edited
Andomar
+2  A: 
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.

hgulyan
This will only select rows with the maximum start date, not the maximum start date per barcode
Andomar
Changed, thanks:)
hgulyan