Max (SQL-Server)

Question

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.

Check this answer to a similar question changing revision into startdate: stackoverflow.com/questions/95866/select-max-in-group — DavGarcia
– DavGarcia, Commented May 17, 2010 at 6:16
@rah.deex: I have reformatted your question, please have a look at the new version to learn about formatting (stackoverflow.com/editing-help) — Peter Lang
– Peter Lang, Commented May 17, 2010 at 6:19

hgulyan · Accepted Answer · 2010-05-17 11:11:18Z

2

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.

edited May 17, 2010 at 11:11

answered May 17, 2010 at 8:25

hgulyan

8,2799 gold badges53 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andomar Over a year ago

This will only select rows with the maximum start date, not the maximum start date per barcode

Andomar · Accepted Answer · 2010-05-17 09:57:40Z

1

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.

edited May 17, 2010 at 9:57

answered May 17, 2010 at 8:21

Andomar

239k55 gold badges387 silver badges412 bronze badges

1 Comment

shahkalpesh Over a year ago

I think, you are missing DESC next to ORDER BY startdate

Collectives™ on Stack Overflow

Max (SQL-Server)

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related