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!
LVL 22
Russ SuterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
PortletPaulEE Topic AdvisorCommented:
>>"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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.