• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

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.
Data SampleIf I recall there was something with PARTITION BY but it's not coming to me. HELP!
0
Russ Suter
Asked:
Russ Suter
2 Solutions
 
Pawan KumarDatabase ExpertCommented:
Pls use this..Need max from PRECEDENCE

SELECT ACCOUNT_NUMBER, SHOULD_INCLUDE, MAX(PRECEDENCE) AS PRECEDENCE 
FROM YourTableName
GROUP BY ACCOUNT_NUMBER, SHOULD_INCLUDE

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Sorry updated.

SELECT ACCOUNT_NUMBER, MIN(SHOULD_INCLUDE) SHOULD_INCLUDE , MAX(PRECEDENCE) AS PRECEDENCE 
FROM YourTableName
GROUP BY ACCOUNT_NUMBER

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
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.PRECEDENCE

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Russ SuterAuthor Commented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
Have you tried my last solution ? What is the name of the table.
0
 
Russ SuterAuthor Commented:
It's not a table, it's a fairly long and complex query. Joining the query on itself would be a problem.
0
 
Pawan KumarDatabase ExpertCommented:
We can also use Partition by logic like below -

SELECT ACCOUNT_NUMBER, SHOULD_INCLUDE, PRECEDENCE FROM
(
	SELECT *, ROW_NUMBER() OVER(PARTITION BY ACCOUNT_NUMBER ORDER BY PRECEDENCE DESC) AS rnk 
	FROM (YourQuery)
)k 
WHERE rnk = 1

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Hey Russ, Have you tried my last suggestion ?
0
 
arnoldCommented:
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.
0
 
PortletPaulfreelancerCommented:
>>"If I recall there was something with PARTITION BY but it's not coming to me. HELP!"

You almost gave us the answer in the question: It's the OVER() clause that permits PARTITION BY and ORDER BY e.g.

ROW_NUMBER() OVER(PARTITION BY .... ORDER BY ... )

and ROW_NUMBER() returns an integer per row always commencing with 1 at each partition, never repeating an integer within a partition, incrementing by 1 according the the order by

It is exceptionally handy for locating whole rows that represent the "most", or "least", or "latest" or "earliest". In your case:

ROW_NUMBER() OVER(PARTITION BY ACCOUNT_NUMBER ORDER BY PRECEDENCE DESC, SHOULD_INCLUDE DESC) as rn

Note it is a good idea to be as specific as possible regarding the ordering, here should_include is used as a "tie breaker"

As Pawan shows in his most recent example above, you cannot reference a function like this within the where clause of the same query, so nest your query and filter the subquery instead using the column alias you assign. Typically I use rn (row number) for that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now