Select Distinct with multiple fields 1

[RSX02]

Hi
I would like to do a Select Distinct only on one field but I want my select contain more than the field that I want to do a Distinct with.
For example: I have theses records
seq rev first_name last_name
1 0 John Smith
1 1 Tina Smith
1 2 Joe Smith
2 0 Bob Smith
2 1 Mia Smith

The records I would like to retrieve is
Seq Rev
1 2
2 1
(Because I would like to retrieve the last revision for a seq)
I tried this:
Select distinct(Seq), revision, first_name, last_name from Customer
but I still get all records.
Any idea?
Thanks in advance

 

[amorous]

Try this:

SELECT t1.*
FROM MyTable t1
WHERE t1.rev In (select top 1 t2.rev from MyTable t2 where t2.seq = t1.seq ORDER BY t2.rev DESC)

-VJ

 

[RSX02]

good thank you!

 

[Ginish]

Dear all...

We can solve this by following query also...

select A.* from UserMst A
inner join (select Seq,Max(Rev) as Rev from UserMst group by Seq) B
on A.Seq=B.Seq and A.Rev=B.Rev
order by A.Seq,A.Rev

Bye

Ginish