SQL brain dead ?

[Fishface]

table
cols
id_num group num
902 738
456 738
012 738
921 602
223 602
114 559
948 559
335 559

want to pick ONE row in EACH group_num (there are 250 groups)

so only 902, 921, 114

in one query

how? sorry gone brain -

[bored]

Which DB are you using? I don't think there is an efficient way to do it but you could do:

select * from mytable where id_num in (select min(id_num) from table group by group_num)

[Fishface]

Which DB are you using? I don't think there is an efficient way to do it but you could do:

select * from mytable where id_num in (select min(id_num) from table group by group_num)

thanks, tried that one doesn't work cos you need id_num in group clause

SQL Server 2000

it looks simple to do - there must be a way to do this easily

humpf - looks like t-sql.....

[chicane]

select min(id_num) from tbl group by group_num

or

select min(id_num), group_num from tbl group by group_num

(if you need group_num included)

Or am i missing the point?
Assuming that the column types are integers and it's ok to get the lowest id_num from each group.

[Fishface]

select min(id_num) from tbl group by group_num

or

select min(id_num), group_num from tbl group by group_num

(if you need group_num included)

Or am i missing the point?
Assuming that the column types are integers and it's ok to get the lowest id_num from each group.

doesn't work because columns in the select statment have to be in the group clause - in sql svr 2000.

[chicane]

Both queries work in SQL Server 2000 here.

select min(id_num) as minidnum, group_num from tbl group by group_num

minidnum    group_num   
----------- ----------- 
114         559
223         602
12          738

(3 row(s) affected)

[bored]

thanks, tried that one doesn't work cos you need id_num in group clause

It does work because I'm selecting min(id_num), an aggregate function, and not just id_num.

[Spacecadet]

if you only want the id_num column

select id_num
  from (
select min(id_num) as id_num, group_num
  from table
  group by group_num) as T1

[chicane]

if you only want the id_num column
....

Or just:

select min(id_num) as id_num from tbl group by group_num

[Spacecadet]

Or just:

select min(id_num) as id_num from tbl group by group_num

I stand very much corrected!