T-SQL Question

[klabu]

How to write an equivalent SQL like this (which works on Oracle):

SELECT * FROM sometable
WHERE rec_id, rec_sequence
IN (SELECT rec_id, MAX( rec_sequence) FROM alppremh GROUP BY rec_id)

thanks

[klabu]

Re: T-SQL Question

>
SELECT * FROM sometable
WHERE rec_id, rec_sequence
IN (SELECT rec_id, MAX( rec_sequence) FROM alppremh GROUP BY rec_id)
>
thanks
>

edit:

SELECT * FROM sometable
WHERE rec_id, rec_sequence
IN (SELECT rec_id, MAX( rec_sequence) FROM alppremh GROUP BY rec_id)
and rec_id = 4883493

[Aaron Bertrand [SQL Server MVP]]

Re: T-SQL Question

SELECT s.*
FROM sometable s
INNER JOIN
(
SELECT rec_id,
rs = MAX(rec_sequenc e)
FROM alppremh
GROUP BY rec_id
) a
ON s.rec_id = a.rec_id
AND s.rec_sequence = a.rs;





"klabu" <noone_at_gmail _dot_comwrote in message
news:12lf5tld76 k2o21@corp.supe rnews.com...

How to write an equivalent SQL like this (which works on Oracle):
>
SELECT * FROM sometable
WHERE rec_id, rec_sequence
IN (SELECT rec_id, MAX( rec_sequence) FROM alppremh GROUP BY rec_id)
>
thanks
>

[Steve Kass]

Re: T-SQL Question

[responding only to comp.databases. ms-sqlserver, due to newsreader limitations]

Another option, probably more efficient in most cases, is

select * from sometable
where rec_sequence = (
select max(rec_sequenc e)
from alppremh
where rec_id = 4883493
)
and rec_id = 4883493

Steve Kass
Drew University

http://www.stevekass.com

klabu wrote:

How to write an equivalent SQL like this (which works on Oracle):
>
SELECT * FROM sometable
WHERE rec_id, rec_sequence
IN (SELECT rec_id, MAX( rec_sequence) FROM alppremh GROUP BY rec_id)
and rec_id = 4883493

>
>