T-SQL for the following?

[Mark Probert]

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.

[KenJ]

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 <[e-mail]:

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.

[Ed Murphy]

Mark Probert wrote:

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?

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

[Mark Probert]

Hi, Ed..

You 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

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

-mark.