CODEWITHSUNDEEP
sqlsql-servert-sql
fra
fra

Reputation: 186

Retrieve most common item name and its highest price from multiple categories by one SQL query

I have a Template table which contains multiple TypeIDs for each TemplateID. I have an Items table whith ItemName, ItemPrice and TypeID. I need to get the most common ItemName and the highest price within that ItemName of every TypeID which attached to the selected Template. My query doesn't work as obviously it returns multiple rows from the ItemsTable for each TypeID because of the inner joi but i can't find the way to write the query. My query :

select t.TypeID, t.TemplateID, n.ItemName, n.MaxPrice
from Templates t 
inner join (select count(i.ItemName) as foundn, i.ItemName, max(i.ItemPrice) as MaxPrice, i.TypeID
        from Items i
        group by i.TypeID, i.ItemName) n on n.TypeID=t.TypeID
WHERE t.TemplateID=2;

The result I would like to see is like:

TemplateID 1:

...etc

Upvotes: 0

Views: 99

Answers (1)

gotqn
gotqn

Reputation: 43646

Try this:

WITH DataSource AS
(
    select t.TypeID, t.TemplateID, n.ItemName, n.MaxPrice, ROW_NUMBER() OVER (PARTITION BY t.TemplateID, t.TypeID ORDER BY foundn DESC) as rn
    from Templates t 
    inner join 
    (
        select count(i.ItemName) as foundn, i.ItemName, max(i.ItemPrice) as MaxPrice, i.TypeID
        from Items i
        group by i.TypeID, i.ItemName
    ) n 
        on n.TypeID=t.TypeID
    WHERE t.TemplateID=2
)
SELECT *
FROM DataSource
WHERE rn = 1

The idea is to use ROW_NUMBER to generate row ID for each template - type pair starting with the one with biggest foundn. Then, display only these with row ID = 1.

Upvotes: 1

Related Questions