Latest Instance of Record in Table

[Bobbber]

Hi,

Let's pretend I have a table like this :

ID Sequence Status
1 1 Y
1 2 Y
1 3 N
2 1 N

etc...

I want a single query to return the latest status. For example for ID=1 this would be N.

At the moment I have something like this to work that out :

SELECT Status FROM Table WHERE ID=1 AND Sequence IN (SELECT MAX(Sequence) FROM Table WHERE ID=1)

Surely there must be a more elegant way of doing this?

I've tried things like :

SELECT ID, Status FROM Table
WHERE ID=1
GROUP BY ID
HAVING Sequence=MAX(Sequence)

But to no avail!!!!

In actual fact, this table is foreign, joined to another which makes things a little more complex - but if someone can crack the problem above, I can use it to fix the problem as a whole.

Thanks for helping (in advance!)

Bob

 

[vongrunt]

For single ID:

select top 1 id, status
from myTable
where id = 1
order by Sequence desc

For many IDs at once:

select A.ID, A.status
from myTable A
inner join 
(	select ID, max(Sequence) as maxseq
	from myTable 
	group by ID
) B
on A.ID=B.ID and A.Sequence=B.maxseq

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[VolkerStamme]

Bob,
I've tried

SELECT ID, Status FROM table ;
  WHERE Str( id ) + Str( sequence ) in 
  (SELECT Str( id ) + str( Max(sequence)) as sequence
    FROM table GROUP BY id )

to get a unique field from id and sequence in the subselect and it seems to be working.

Volker/