Reply to: SQL brain dead ?

[bored]

select 902, 921, 114

HTH

at least get it right

select * where id_num in (902, 921, 114)

[MikeC1408]

select 902, 921, 114

HTH

[DimPrawn]

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 -

select 902, 921, 114

HTH

[MarillionFan]

Oh good grief

[Fishface]

In which case use the subselect:

select id_num, group_num, document_name from my_table where id_num in (select max(id_num) from my_table group by group_num)

(I assume here that id_num is your primary key)

Yes, I got to this too thanks - however, it took me ages!

I am either getting old or really not giving a fk about the work to be done or the deadline.

...should smarten myself up a bit...

[bored]

In which case use the subselect:

select id_num, group_num, document_name from my_table where id_num in (select max(id_num) from my_table group by group_num)

(I assume here that id_num is your primary key)

[Fishface]

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

Apologies Bored - you are the aggr func this is correct - I was being dimwitted.

I have manage to do what I want with 50 lines of code selecting ids into an array, looping distinct groups, selecting first id in array and re-selecting from the group using that max(id) - it works...

reason being is I had to select a the id, group_num & a 'unique' *document name* from the max(id) in each group. Therefore, using the agg func/group by doesn't work because the doc name col has to go in the group clause.

Sorry, it was friday eve and gone snow blind and thoughts of beer were bringing clarity to my worldview.

Still, the SQL is puzzling me - I would like a crisp 4 line way to do this...

I will percyvere...

maybe HAVING COUNT(*) = 1...

[Weltchy]

If your after a single value which is either the minimum or maximum in that group range, then min() or max() will do. If you want to pick the first value that appears again the group value, you'll need to use a rank() function, which you will find in SQL-2005 but not 2000.

The problem is, if you want to order the data by the order in which it appears in a standard SELECT query, as specifying an order by will require you to use a row_number()esque function.

Perhaps if you define your requirement more exactly, then a suitable solution will be presented...

[where did my id go?]

unless I misunderstand, then he wants the max() rather than the min().

or at least thats what his example implies - although he doesnt actually say so. But I cant see the point of choosing a random member of each group - surely there is a criteria for that selection?

[Bumfluff]

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 -

If only one row per group_num and your not bothered about what you return as the id_num for that group_num use

Select distinct(group_num), '1' from table

or if you dont want the id number just,


Select distinct(group_num) from table

[Spacecadet]

he said ONE row (capitalisation was his). The words first, max and min did not come into it.
Using the min() function happens to be the simplest method of retrieving one row which happens to have the smallest value

[bored]

There is no such thing as the "first" record as records in relational databases are unordered.

[TravelMonkey]

Ok, so maybe Im missing the point here and dont expect any code cos Im a support monkey not a database monkey, but he is not looking for the min value, he is looking for the value of id_num on the first record when group_num changes. hence the example given from original post :

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

so only 902, 921, 114 note these are not the min values, but the first values in the record when group_num changes???

[Spacecadet]

Or just:

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

I stand very much corrected!

[chicane]

if you only want the id_num column
....

Or just:

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