Re: [firebird-support] Get ID of record with minsort

2015-06-25 Thread Helen Borrie hele...@iinet.net.au [firebird-support] 
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

2015-06-25 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
 
 
W dniu 2015-06-25 09:18:48 użytkownik josef.gschwendt...@quattro-soft.de 
[firebird-support] firebird-support@yahoogroups.com 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

2015-06-25 Thread Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] 
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 Table
Order by Sort_Column
Rows 1

If you want all rows returned:

Select ID_column
From Table t1
Where not exists(select * from Table t2 where t1.Sort  t2.Sort)

Alternatively, you could use

With tmp(Sort) as
(select min(Sort) from table)
Select t.ID_Column
From tmp
Join table t on tmp.Sort = t.Sort

Set

RE: [firebird-support] Get ID of record with minsort

2015-06-25 Thread josef.gschwendt...@quattro-soft.de [firebird-support] 
Hi Set,

thank you very much.

Row 1 is the solution... 
It would have been so easy.

Sepp