[firebird-support] Get ID of record with minsort
Hi, I have a table with one ID-Column and one Sort-Column. I need the ID from the record with the lowest sort-number. Sortnumbers are not unique (can be doubled). Can I do this with one SQL-Statement? Thank you for your help. Regards, Josef
Re: [firebird-support] Get ID of record with minsort
At 07:18 p.m. 25/06/2015, josef.gschwendt...@quattro-soft.de [firebird-support] wrote: >Hi, > >I have a table with one ID-Column and one Sort-Column. > >I need the ID from the record with the lowest sort-number. >Sortnumbers are not unique (can be doubled). > >Can I do this with one SQL-Statement? That's not enough information. Do you want all of the IDs with the lowest sort-number? Or just any single ID with the lowest sort-number? Or the highest (or lowest) ID with the lowest sort-number. You can get any of those. with one statement..so which one? Helen
Re: [firebird-support] Get ID of record with minsort
W dniu 2015-06-25 09:18:48 użytkownik josef.gschwendt...@quattro-soft.de [firebird-support] napisał: Hi, I have a table with one ID-Column and one Sort-Column. I need the ID from the record with the lowest sort-number. Sortnumbers are not unique (can be doubled). Can I do this with one SQL-Statement? Thank you for your help. Regards, Josef Hi, simplest if i understand you corectly SELECT T.FLD_ID, T.FLD_SORT, (SELECT FIRST 1 T2.FLD_ID FROM TEST T2 WHERE T2.FLD_ID=T.FLD_ID ORDER BY T2.FLD_SORT) FROM TEST T or maybe you want? SELECT DISTINCT (SELECT FIRST 1 T2.FLD_ID FROM TEST T2 WHERE T2.FLD_ID=T.FLD_ID ORDER BY T2.FLD_SORT) FROM TEST T but you got only 1 ID record if sort column will be doubled regards, Karol Bieniaszewski
RE: [firebird-support] Get ID of record with minsort
>I have a table with one ID-Column and one Sort-Column. > >I need the ID from the record with the lowest sort-number. >Sortnumbers are not unique (can be doubled). > >Can I do this with one SQL-Statement? If you want one row returned: Select ID_column From Order by Sort_Column Rows 1 If you want all rows returned: Select ID_column From t1 Where not exists(select * from t2 where t1.Sort > t2.Sort) Alternatively, you could use With tmp(Sort) as (select min(Sort) from ) Select t.ID_Column From tmp Join t on tmp.Sort = t.Sort Set
RE: [firebird-support] Get ID of record with minsort
Hi Set, thank you very much. Row 1 is the solution... It would have been so easy. Sepp