October 8, 2003 at 9:45 am
id name
1 david
2 chris
3 Bob
4 chris
5 chris
6 anna
7 bob
8 chris
9 david
10 john
11 john
12 anna
I want to get the minimum id of each name
I need the result to be like this
1 david
2 chris
3 bob
6 anna
10 john
can u please tell me the query to accomplish this.
Thanks.
October 8, 2003 at 9:53 am
select
t1.id, t1.name
from your_table t1
where t1.id in (select min(t2.id) from your_table t2 where upper(t2.name) = upper(t1.name))
CVM.
October 8, 2003 at 10:03 am
SELECT MIN(Id), Name
FROM TheTable
GROUP BY Name
--Jonathan
Edited by - jonathan on 10/08/2003 10:03:09 AM
--Jonathan
October 9, 2003 at 9:47 am
In general, if you need more data from the one record in the group, use a derived table to get the key and join. For example,
SELECT A.Id, A.Name, A.InertDate, A.UpdateDate, A.PaidAmount
FROM TheTable A
INNER JOIN (
SELECT MIN(Id) as Id
FROM TheTable
GROUP BY Name
) B ON
B.Id = A.Id
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply