CODEWITHSUNDEEP
sqlsql-servermax
Natali
Natali

Reputation: 59

How to get the records from inner query results with the MAX value

The results are below. I need to get the records (seller and purchaser) with the max count- grouped by purchaser (marked with yellow)

enter image description here

Upvotes: 1

Views: 48

Answers (3)

SovereignSun
SovereignSun

Reputation: 202

SELECT T2.MaxCount,T2.purchaser,T1.Seller FROM <Yourtable> T1
Inner JOIN
(
Select  Max(Count) as MaxCount, purchaser
FROM <Yourtable>
GROUP BY Purchaser
)T2
On T2.Purchaser=T1.Purchaser AND T2.MaxCount=T1.Count

First you select the Seller from which will give you a list of all 5 sellers. Then you write another query where you select only the Purchaser and the Max(count) grouped by Purchaser which will give you the two yellow-marked lines. Join the two queries on fields Purchaser and Max(Count) and add the columns from the joined table to your first query.

I can't think of a faster way but this works pretty fast even with rather large queries. You can further-by order the fields as needed.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269573

You can use window functions:

with q as (
      <your query here>
     )
select q.*
from (select q.*,
             row_number() over (order by seller desc) as seqnum_s,
             row_number() over (order by purchaser desc) as seqnum_p
      from q
     ) q
where seqnum_s = 1 or seqnum_p = 1;

Upvotes: 1

Yahya Mukhtar
Yahya Mukhtar

Reputation: 474

Try this:

SELECT  COUNT,seller,purchaser  FROM YourTable ORDER BY seller,purchaser DESC

Upvotes: 0

Related Questions