Russ Suter
asked on
Query to select only the row with max value
I'm a little rusty on this stuff. I need a query that will return only the rows with the maximum value in a given column. In the below example, there are 3 columns, ACCOUNT_NUMBER, SHOULD_INCLUDE, PRECEDENCE. I want to select only those rows where ACCOUNT_NUMBER is unique and PRECEDENCE is the highest possible value. This screenshot should describe what I need.
If I recall there was something with PARTITION BY but it's not coming to me. HELP!
If I recall there was something with PARTITION BY but it's not coming to me. HELP!
Sorry updated.
SELECT ACCOUNT_NUMBER, MIN(SHOULD_INCLUDE) SHOULD_INCLUDE , MAX(PRECEDENCE) AS PRECEDENCE
FROM YourTableName
GROUP BY ACCOUNT_NUMBER
I think this will nail it.
SELECT k1.* FROM
(
SELECT ACCOUNT_NUMBER, MAX(PRECEDENCE) AS PRECEDENCE
FROM YourTableName
GROUP BY ACCOUNT_NUMBER
)k INNER JOIN YourTableName k1 ON k.ACCOUNT_NUMBER = k1.ACCOUNT_NUMBER
AND k.PRECEDENCE = k1.PRECEDENCEASKER
First solution works in this particular case but not in all cases.
The second solution is problematic in that "YourTableName" isn't a table but a rather complex query.
The second solution is problematic in that "YourTableName" isn't a table but a rather complex query.
Have you tried my last solution ? What is the name of the table.
ASKER
It's not a table, it's a fairly long and complex query. Joining the query on itself would be a problem.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hey Russ, Have you tried my last suggestion ?
The difficulty you have two criteria but you have three data points which is what causes the issue, what is the basis to pick should_onclude.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window