• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

SQL brain dead ?

Fishface

More time posting than coding
Joined
Jan 19, 2007
Messages
467
Likes received
1
Thanks received
2
Location
UPS
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 -
 
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)
 
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.....
 
Last edited: 19:11
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.
 
Last edited: 19:31
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.
 
Both queries work in SQL Server 2000 here.

Code:
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)
 
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.
 
if you only want the id_num column

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

Advertisers

Back
Top