max date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

max date

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-11-02 : 12:30:47

I have a table:


   ID          Price              date
   1001	       100.00          01/01/2004
   1002	       101.00          02/01/2004
   1001        100.00          03/11/2004
   1003         56.98          05/23/2004
   1001        100.00          07/13/2004
   1002        101.00          06/04/2004
   1004        78.67           08/11/2004

I would like to return ID and price that has most recent date. The output should looks like:


   ID          Price              date
 1001         100.00           07/13/2004
 1002         101.00           06/04/2004
 1003	      56.98            05/23/2004
 1004	      78.67            08/11/2004

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-02 : 12:41:19

[code]select ID, price, max(date) date
from t
group by id,price
order by id[/code]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-02 : 13:21:06

that doesn't do it, Ehorn.

you need to use something like this:


select a.*
from YourTable a
inner join
  (select ID, Max(date) as MaxDate from yourtable group by ID) b
on
  a.ID = b.ID and a.Date = b.MaxDate

there are many other ways to put the statement together -- i like using the join. some like doing it like this:

select a.*
from YOurTable a
where Date = (select Max(Date) from Yourtable b where a.id = b.id)

- Jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-02 : 13:27:57

What am I missing here Jeff?

EDIT: Scratch the question. The sample data caused me to overlook a potential difference in price (Gotta get it outta the group by).

Nice catch Jeff :)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-02 : 13:34:01

Yours gave the max date for the id/price. Jeff's gives the rec with the max date for that id.
With the data given the results are the same put the question hints that Jeff's solutions are what is wanted but maybe your's is - who knows.
On re-readng I'm not sure what's wanted but the latest rec for the id is more common.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources