By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,357 Members | 3,557 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,357 IT Pros & Developers. It's quick & easy.

Selecting rows representing highest value for columname1 for each columname2

P: 3
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.test_StagingCortexCommisionTurnover cct
WHERE ActionID = (SELECT MAX(ActionID)
FROM comSR_dev.dbo.test_StagingCortexCommisionTurnover cct1
WHERE cct.TradeID = cct1.TradeID)

Is there something logically wrong or just my bad sql
Mar 26 '08 #1
Share this Question
Share on Google+
3 Replies


10K+
P: 13,264
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.test_StagingCortexCommisionTurnover cct
WHERE ActionID = (SELECT MAX(ActionID)
FROM comSR_dev.dbo.test_StagingCortexCommisionTurnover 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.
Mar 26 '08 #2

P: 3
Am Using SQL server 2000
Thanks
Mar 27 '08 #3

ck9663
Expert 2.5K+
P: 2,878
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.test_StagingCortexCommisionTurnover cct
WHERE ActionID = (SELECT MAX(ActionID)
FROM comSR_dev.dbo.test_StagingCortexCommisionTurnover cct1
WHERE cct.TradeID = cct1.TradeID)

Is there something logically wrong or just my bad sql

Will this work:


Expand|Select|Wrap|Line Numbers
  1. slect TradeID, max(ActionID) from YourTable
  2. group by TradeID
  3.  
-- CK
Mar 27 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.