Subject Re: [firebird-support] Select question
Author Helen Borrie
At 02:13 PM 9/07/2004 -0400, you wrote:
>Given a table
>name sequence otherColumns...
> A 999
> A 998
> B 999
> B 998
> B 997
> C 999
>how can I get back only rows two five and six, plus their associated
>other columns?
>My immediate thought was to use GROUP BY and get the MIN of sequence,
>but of course that wouldn't give me the other columns of that row.

Assuming Name is unique, try these:

1. Getting the grouped value via a correlated subquery:

(select min (t2.sequence) from ThisTable t2
where =
group by ) as MinSequence,
from ThisTable t1
where in ('A', 'B', 'C')
and t1.sequence =
(select min (t3.sequence) from ThisTable t3
where =

Getting it by grouping and restricting a re-entrant inner join:

min (t2.sequence).
t1.blah2, ....
from ThisTable t1
join ThisTable t2 on =
where in ('A', 'B', 'C')
group by, t1.blah1, t1.blah2...[all specified t1.fields]
having min(t2.sequence) = t1.sequence

Performance in either case will be less than stellar.

Didactic footnote:
Ideally, do the joins and correlations on atomic keys. Keys on names are
non-atomic and notoriously unreliable. You can achieve uniqueness with
"John Smith" and "JOHN SMITH" but the question left begging is "Are we
looking at one john-smith-entity or two?"