Hi All,

I have the following table;

Corp_ID Stacking Total
100 10 125
200 10 250
300 20 150
400 20 325

Based on these records setup, I would like to select the records with highest TOTAL
Within STACKING column.

I tried the following query, but it didn’t work.
SELECT *
    FROM #TABLE_1 AS t1
    WHERE t1.Corp_id not in
        (SELECT t3.Corp_id
            FROM (SELECT t2.Stacking, MAX(Total)
          FROM TABLE_1_t AS t2 GROUP BY Stacking) AS t3)


Thanks for your help,
:)


MCinar

Love all the creatures because of the creator.

__________________
MCinar

Love all the creatures because of the creator.

maybe you can do something like:

SELECT * FROM <<table>> t1
INNER JOIN
(SELECT Stacking, MAX(Total) AS MaxTotal
FROM <<table>>
GROUP BY Stacking) AS t2
ON t1.Stacking = t2.Stacking AND t1.Total = t2.MaxTotal

but I'm not sure what you'll get if you have 2 or more records with the same Stacking and Total values.

hth
Phil

Thank you.

MCinar

Love all the creatures because of the creator.