SQL - group by with two colums agregation problem

My today's problem is that I have a table that has rows like that:

ID NAME NUMBER IDN
1  dsad 500600 12
1  dsad 600700 13
2  kkkk 111222 56
2  kkkk 333232 57

开发者_运维百科in one ID example 1 i have 2 identical names 2 different numbers and different IDN.

What i want is to extract single row For each Id where the idn value is the smaller one. So I want to have sommething like that

1  dsad  500600 12
2  kkkk  111222 56

Is it posible to write a single sql to have that result? I tried to group that by ID and NAME and have the min(IDN) but I'm stuck with the number field :) any ideas?

You were almost there, just add the MIN(Number) field.

SELECT  ID
        , NAME
        , MIN(NUMBER)
        , MIN(IDN)
FROM    ATable
GROUP BY
        ID
        , NAME

In response to comment

Following would get you the records with the MIN(IDN), regardless what the number for that specific record is.

SELECT  t.*
FROM    ATable t
        INNER JOIN (        
          SELECT  ID, IDN = MIN(IDN)
          FROM    ATable
          GROUP BY ID
        ) tmin ON tmin.ID = t.ID
                  AND tmin.IDN = t.IDN

DECLARE @TABLE table (ID int, [NAME] varchar(100),NUMBER int ,IDN int)
insert into @TABLE SELECT 1,'dsad',500600,12
insert into @TABLE SELECT 1,'dsad',600700, 13
insert into @TABLE SELECT 2,'kkkk',111222, 56
insert into @TABLE SELECT 2,'kkkk',333232, 57

select t.ID, t.[Name], t.Number, t.IDN
from (
   select [NAME],min(IDN) as minIDN
   from @TABLE group by [NAME]
) as x inner join @TABLE as t on t.[Name]=x.[Name] and t.IDN = x.minIDN;

Version that only uses a left join and no subqueries, with SQlite3 and a shell script since I don't have anything other at hand ATM:

#!/bin/sh

rm -f test.sqlite
sqlite3 test.sqlite << AAA
CREATE TABLE test (id int, name text, number int, idn int);
INSERT INTO test VALUES(1,'dsad',500600,12);
INSERT INTO test VALUES(1,'dsad',600700,13);
INSERT INTO test VALUES(2,'kkkk',111222,56);
INSERT INTO test VALUES(2,'kkkk',333232,57);
INSERT INTO test VALUES(1,'dsad',600700,9);
INSERT INTO test VALUES(2,'kkkk',333232,59);
INSERT INTO test VALUES(2,'cccc',333232,59);
SELECT a.* FROM test a
    LEFT JOIN test b ON
            a.id=b.id AND
            a.name=b.name
            AND a.idn > b.idn
            WHERE b.id IS NULL;
AAA
# Result:
# 1|dsad|600700|9
# 2|cccc|333232|59
# 2|kkkk|111222|56

Can anyone comment on where the performance is better? I think that matters, too!