1

I have a control table, where Prices with Item number are tracked date wise.

id  ItemNo  Price  Date
---------------------------
1   a001    100    1/1/2003
2   a001    105    1/2/2003
3   a001    110    1/3/2003
4   b100    50     1/1/2003
5   b100    55     1/2/2003
6   b100    60     1/3/2003
7   c501    35     1/1/2003
8   c501    38     1/2/2003
9   c501    42     1/3/2003
10  a001    95     1/1/2004

This is the query I am running.

SELECT  pr.*
FROM    prices pr
        INNER JOIN
        (
            SELECT  ItemNo, max(date) max_date
            FROM     prices
            GROUP   BY ItemNo
        ) p ON  pr.ItemNo = p.ItemNo AND
                pr.date = p.max_date
                order by ItemNo ASC

I am getting below values

id  ItemNo   Price  Date
------------------------------
10  a001     95     2004-01-01
6   b100     60     2003-01-03
9   c501     42     2003-01-03

Question is, is my query right or wrong? though I am getting my desired result.

iceblade
  • 611
  • 7
  • 20
Devilpor
  • 19
  • 4

1 Answers1

2

Your query does what you want, and is a valid approach to solve your problem.

An alternative option would be to use a correlated subquery for filtering:

select p.*
from prices p 
where p.date = (select max(p1.date) from prices where p1.itemno = p.itemno)

The upside of this query is that it can take advantage of an index on (itemno, date).

You can also use window functions:

select *
from (
    select p.*, rank() over(partition by itemno order by date desc) rn
    from prices p
) p
where rn = 1

I would recommend benchmarking the three options against your real data to assess which one performs better.

GMB
  • 216,147
  • 25
  • 84
  • 135