Selecting rows representing highest value for columname1 for each columname2

[magikminox]

Hi Folks

I have a table in this format

TradeID ActionID
58096 3664
58096 3665
58097 3685
58097 3687
…..

I want to select all the rows but only the rows that represent the highest ActionIDs for each TradeID

For some reason this query is not giving me the correct results

SELECT cct.TradeID,cct .ActionID
FROM comSR_dev.dbo.t est_StagingCort exCommisionTurn over cct
WHERE ActionID = (SELECT MAX(ActionID)
FROM comSR_dev.dbo.t est_StagingCort exCommisionTurn over cct1
WHERE cct.TradeID = cct1.TradeID)

Is there something logically wrong or just my bad sql

[r035198x]

Hi Folks

I have a table in this format

TradeID ActionID
58096 3664
58096 3665
58097 3685
58097 3687
…..

I want to select all the rows but only the rows that represent the highest ActionIDs for each TradeID

For some reason this query is not giving me the correct results

SELECT cct.TradeID,cct .ActionID
FROM comSR_dev.dbo.t est_StagingCort exCommisionTurn over cct
WHERE ActionID = (SELECT MAX(ActionID)
FROM comSR_dev.dbo.t est_StagingCort exCommisionTurn over cct1
WHERE cct.TradeID = cct1.TradeID)

Is there something logically wrong or just my bad sql

Hi and welcome to TSDN. Hope you'll have a great time here.
You've posted your question in the introductions area. Which database are you using so that I can move this to the relevant forum.

Remember to read the posting guidelines.

[magikminox]

Am Using SQL server 2000
Thanks

[ck9663]

Hi Folks

I have a table in this format

TradeID ActionID
58096 3664
58096 3665
58097 3685
58097 3687
…..

I want to select all the rows but only the rows that represent the highest ActionIDs for each TradeID

For some reason this query is not giving me the correct results

SELECT cct.TradeID,cct .ActionID
FROM comSR_dev.dbo.t est_StagingCort exCommisionTurn over cct
WHERE ActionID = (SELECT MAX(ActionID)
FROM comSR_dev.dbo.t est_StagingCort exCommisionTurn over cct1
WHERE cct.TradeID = cct1.TradeID)

Is there something logically wrong or just my bad sql

Will this work:

slect TradeID, max(ActionID) from YourTable
group by TradeID

-- CK