EXPERT RESPONSE
The first thing we need to establish
is what you mean by the "second" tuple.
There must be a sequence, a way of ordering
the rows, that allows us to determine which
one is first, and which one is second.
By your example, I shall assume that "first"
means the lowest collating NAME.
Now for the problem of finding the "second" one.
There may be other ways to do it, but here's how I
would approach this problem.
The "first" one is the lowest name, and we can get this
with the MIN() function and grouping on NO:
select NO, min(NAME)
from yourtable
group
by NO
The "second" one is trickier. It is the lowest one that
isn't the first one. But this time, instead of using GROUP BY,
we use another method to produce grouping, the correlated subquery.
select NO, NAME
from yourtable XX
where NAME =
( select min(NAME)
from yourtable
where NO = XX.NO
and NAME >
( select min(NAME)
from yourtable
where NO = XX.NO
)
)
To see how this works, consider any row in the outer query.
Using the correlation variable XX to refer to
this row in the outer query, the innermost subquery gets the lowest
name for all rows with the same value of NO as the XX row
being considered. This lowest value is used by the next outer
subquery, which gets the lowest name that's greater than the lowest
which was found by the innermost subquery. In other words, the second lowest.
Then the outer query gets the row that has that second lowest name.
It's easy when you see it explained, but kind of hard to come up with
on your own if you've never seen it before. For More Information
|
