select value based on other fields max value

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)

select value based on other fields max value

Author

Topic

cornall
Posting Yak Master

148 Posts

Posted - 2009-04-17 : 11:07:38

Hi,

If I have a table

type, date, value
1 2009-01-01 10
1 2009-01-03 11
2 2009-01-01 14
2 2009-01-03 10

and I join to this table based on type how do I select the value for the max(date)

SELECT b.value
FROM a LEFT JOIN b ON a.type = b.type

What do I need to get the value for max date?

Hope this makes sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 11:23:34

if dates wont repeat then


SELECT TOP 1 b.value
FROM a LEFT JOIN b ON a.type = b.type
ORDER BY b.date DESC

and if it repeats

SELECT  b.value
FROM a LEFT JOIN b ON a.type = b.type
WHERE b.date = (SELECT MAX(date) FROM b)

cornall
Posting Yak Master

148 Posts

Posted - 2009-04-17 : 11:26:00

Sorry I need it for each type

so it should return

11 and 14

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 11:32:25

[code]
SELECT a.*
FROM table a
INNER JOIN (SELECT type,MAX(date) AS latest FROM table GROUP BY type)b
ON a.type = b.type
AND a.date=b.latest
[/code]

cornall
Posting Yak Master

148 Posts

Posted - 2009-04-17 : 11:47:34

Thanks again but table a doesn't have date for a.date=b.latest

cornall
Posting Yak Master

148 Posts

Posted - 2009-04-17 : 11:49:16

forget the join how do I get

type, date, value
1 2009-01-01 10
1 2009-01-03 11
2 2009-01-01 14
2 2009-01-03 10

value for each type with max date from the above.

cornall
Posting Yak Master

148 Posts

Posted - 2009-04-17 : 11:59:04

Is it just

SELECT type,value FROM
table WHERE
date IN (SELECT MAX(date) FROM table GROUP BY type)

cornall
Posting Yak Master

148 Posts

Posted - 2009-04-17 : 12:07:57

Sorry ignore my nonsense I see you are joining the table to itself referenced as a then b!!

Thanks for all your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 13:47:08

welcome