CODEWITHSUNDEEP
sqlsql-serverselect
Your Yummy
Your Yummy

Reputation: 153

sql server select query a bit complex

I have a item_prices table with prices. Those prices vary at any time.

I want to display all items where date is highest

ITEM_prices

id | Items_name | item_price | item_date
------------------------------------------
1        A          10         2012-01-01
2        B          15         2012-01-01
3        B          16         2013-01-01
4        C          50         2013-01-01
5        A          20         2013-01-01

I want to display ABC items once each with highest date like as below

id | Items_name | item_price | item_date
-------------------------------------------
3        B          16         2013-01-01
4        C          50         2013-01-01
5        A          20         2013-01-01

Upvotes: 0

Views: 102

Answers (4)

Anup Shah
Anup Shah

Reputation: 1254

when you can use native functions then why to go for any window function or CTE.

    SELECT t1.*
    FROM ITEM_prices t1
    JOIN
    (
        SELECT Items_name,MAX(item_date) AS MaxItemDate
        FROM ITEM_prices
        GROUP BY Items_name
    )t2
        ON t1.Items_name=t2.Items_name AND t1.item_date=t2.MaxItemDate

Upvotes: 1

Hogan
Hogan

Reputation: 70513

This will select all rows with the max date. 

SELECT * 
FROM item_prices
WHERE item_date = (SELECT max(item_date) FROM item_prices)
ORDER BY ID

This will select all rows for each item with the max date for that item.

select id, Items_name, item_price, item_date
from (select items_name, max(item_date) max_item_date
      from item_prices
      group by items_name
     ) ip
where item_date = max_item_date and items_name = ip.items_name

Upvotes: 0

marc_s
marc_s

Reputation: 754268

One approach would be to use a CTE (Common Table Expression) if you're on SQL Server 2005 and newer (you aren't specific enough in that regard).

With this CTE, you can partition your data by some criteria - i.e. your Items_name - and have SQL Server number all your rows starting at 1 for each of those "partitions", ordered by some criteria.

So try something like this:

;WITH NewestItem AS
(
   SELECT 
       id, Items_name, item_price, item_date,
       RowNum = ROW_NUMBER() OVER(PARTITION BY Items_name ORDER BY item_date DESC) 
   FROM 
       dbo.ITEM_Prices
)
SELECT 
   id, Items_name, item_price, item_date
FROM 
   NewestItem
WHERE
   RowNum = 1

Here, I am selecting only the "first" entry for each "partition" (i.e. for each Items_Name) - ordered by the item_date in descending order (newest date gets RowNum = 1).

Does that approach what you're looking for??

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

One way is to use window functions to find the maximum date for each item:

select id, Items_name, item_price, item_date
from (select ip.*,
             max(item_date) over (partition by items_name) as max_item_date
      from item_prices
     ) ip
where item_date = max_item_date;

Upvotes: 0

Related Questions