473,320 Members | 1,965 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Selecting rows representing highest value for columname1 for each columname2

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
3 1144
r035198x
13,262 8TB
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
Am Using SQL server 2000
Thanks
Mar 27 '08 #3
ck9663
2,878 Expert 2GB
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

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

Similar topics

0
by: Alec Smith | last post by:
I have two tables as below: CREATE TABLE domain_types ( type_id INT(4) NOT NULL AUTO_INCREMENT, name VARCHAR(10) UNIQUE NOT NULL, description VARCHAR(75), PRIMARY KEY(type_id) ) TYPE=INNODB...
0
by: Alec Smith | last post by:
I have two tables as below: CREATE TABLE domain_types ( type_id INT(4) NOT NULL AUTO_INCREMENT, name VARCHAR(10) UNIQUE NOT NULL, description VARCHAR(75), PRIMARY KEY(type_id) ) TYPE=INNODB...
19
by: Ben Gribaudo | last post by:
Hello! I am trying to query a MySQL table and retrieve either the earliest or the latest message in each thread. The messages are in a table with MsgID being the primary key and fThreadID...
5
by: Axial | last post by:
Question: How to select columns from Excel-generated XML when some cells are empty. I've found examples where rows are to be selected, but I can't seem to extrapolate from that to selecting...
2
by: Dirtyweeker | last post by:
Hi, I have a database which records fitness test results of pupils. There are the usual name fields and then a series of fields holding results, e.g. field BP1 and field BP2; each of these...
21
by: Jaspreet | last post by:
I was working on some database application and had this small task of getting the second highes marks in a class. I was able to do that using subqueries. Just thinking what is a good way of...
48
by: phillip.s.powell | last post by:
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't ask) I have database Spring with table Students I have database Summer with table Students I am tasked to produce a...
0
by: | last post by:
I am testing the following piece of code: Is it possible to list the row number containing the checked box? After checking the desired box, I would want a message box to popup listing the row...
17
by: pbd22 | last post by:
Hi. How does one return a range of rows. I know that "Top 5" will return rows 0 - 5 but, how do I get 6 - 10? thanks
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.