SEO

#1 (**permalink**) 03-01-2008, 11:34 AM

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

#2 (**permalink**) 03-01-2008, 11:34 AM

>
> 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

#3 (**permalink**) 03-01-2008, 11:34 AM

SELECT s.*
FROM sometable s
INNER JOIN
(
SELECT rec_id,
rs = MAX(rec_sequence)
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_com> wrote in message
news:12lf5tld76k2o21@corp.supernews.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
>

#4 (**permalink**) 03-01-2008, 11:35 AM

[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_sequence)
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

>
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

T-SQL Question