Hi ..

I am geeting myself into many knots around how to do thsi extraction.

There is a table that contains the following:

aID bID cID dID
--------------------------
6 9 28 1
6 2 28 2
6 11 28 3
6 1 27 5
6 9 27 5
6 11 27 5

I am trying to get a result of:

aID bID cID dID
--------------------------
6 9 28 1
6 2 28 2
6 11 28 3
6 1 27 5

So, something like "the set off all values over bID which have the
greatest cID". Any ideas?

Many thanks, in advance,

--
-mark.

Hi Mark,

This query gets the result you were looking for. I don't know if it
works for cases that weren't represented by the numbers in your
example.

SELECT
aID
, min(bID) AS bID
, max(cID) AS cID
, dID
FROM
@tbl
GROUP BY
aID
, dID
ORDER BY
dID

Thanks,

Ken



On Feb 21, 6:21 pm, Mark Probert <probe... (AT) gmail (DOT) com> wrote:

Mark Probert wrote:

select t.aID, t.bID, t.cID, t.dID
from the_table t
join (
select bID, max(cID) max_cID
from the_table
group by bID
) s on t.bID = s.bID and t.cID = s.max_cID

Hi, Ed..

You wrote:

Many thanks.
I have some study to do on that "join" syntax ;-)
--

-mark.