Selecting rows with highest date

Question

I have the following query that throws a result like in the example:

SELECT            P.IdArt, P.IdAdr, P.gDate, P.Price
FROM              dbo.T_PriceData AS P INNER JOIN
                  dbo.T_Adr AS A ON P.IdAdr = A.IdAdr INNER JOIN
                  dbo.T_Stat AS S ON A.IdStat = S.IdStat
GROUP BY          P.IdArt, P.IdAdr, P.gDate, P.Price

  IdArt  IdAdr     gDate      Price
    1     10    01/01/2018     1.25
    1     10    02/01/2018     1.17     
    1     10    03/01/2018     1.18
    2     15    01/01/2018     1.03
    2     18    10/01/2018     0.12
    3     25    12/01/2018     0.98
    3     25    28/01/2018     1.99
    4     30    15/01/2018     2.55
    5     35    08/01/2018     0.11

The final result I want is:

When the IdArt and IdAdr are the same, there should be only one row with the highest date of all rows (CASE IdArt 1)

When IdArt is the same but IdAdr is different, there should be a row with each IdAdr with the highest date for each IdAdr. (CASE IdArt 2)

Price doens't affect anything.

So the final table I would like to have is:

IdArt  IdAdr     gDate      Price
1      10      03/01/2018    1.18
2      15      01/01/2018    1.03
2      18      10/01/2018    0.12
3      25      28/01/2018    1.99
4      30      15/01/2018    2.55
5      35      08/01/2018    0.11

How can I do that? I tried with a having clausule selecting by MAX(gDate) but, of course, I only get one row with the max date from the whole database.

Isn't your first requirement already provided by your second? Isn't this just "every unique combination of idArt and idAdr should produce one output row"? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 11, 2019 at 8:12
@Sami this is a popular question of getting rows with max in one column. Generally, it go to a () IN (); INNER JOIN max_table; .... MAX(price) should not work. — Pham X. Bach
– Pham X. Bach, Commented Jan 11, 2019 at 8:26
@Sami using MAX would return the wrong values; such as the price 1.25 for the date 03/01/2018 on ID 1. — Thom A
– Thom A ♦, Commented Jan 11, 2019 at 8:32

Thom A · Accepted Answer · 2019-01-11 08:27:40Z

2

There are lots of answers out there on how to do this, however, this gets you what you are after:

SELECT TOP 1 WITH TIES
       P.IdArt,
       P.IdAdr,
       P.gDate,
       P.Price
FROM dbo.T_PriceData P
     --INNER JOIN dbo.T_Adr A ON P.IdAdr = A.IdAdr --You don't reference this in the SELECT or WHERE. Why is it here?
     --INNER JOIN dbo.T_Stat S ON A.IdStat = S.IdStat --You don't reference this in the SELECT or WHERE. Why is it here?
ORDER BY ROW_NUMBER() OVER (PARTITION BY P.IdArt, P.IdAdr ORDER BY P.gDate DESC);

Edit: If the JOINs are there to ensure that there are rows in the other tables, then as per the comments I would use EXISTS. If you just use JOIN, and only returning rows from the first table, then you could end up with duplicate rows.

SELECT TOP 1 WITH TIES
       P.IdArt,
       P.IdAdr,
       P.gDate,
       P.Price
FROM dbo.T_PriceData P
WHERE EXISTS (SELECT 1
              FROM dbo.T_Adr A
              WHERE P.IdAdr = A.IdAdr)
  AND EXISTS (SELECT 1
              FROM dbo.T_Stat S
              WHERE A.IdStat = S.IdStat)
ORDER BY ROW_NUMBER() OVER (PARTITION BY P.IdArt, P.IdAdr ORDER BY P.gDate DESC);

edited Jan 11, 2019 at 8:27

answered Jan 11, 2019 at 8:15

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Pham X. Bach Over a year ago

The INNER JOIN is condition that that row has reference in those other two tables. You can only omit it if it's a LEFT JOIN. Or maybe I'm wrong, and the query itself didn't has that intention...

Thom A Over a year ago

I would personally go with an EXISTS then @PhamX.Bach. Otherwise, if these are many-to-one relationships you could end up with multiple duplicate rows.

Thom A Over a year ago

No, that might nothelp here @RadimBača, as then you would have multiple rows for the same date. The OP only wants one.

Nanaki Over a year ago

The first one seems to work fine. Just so I understand... what does the "WITH TIES" clausule there?

Thom A Over a year ago

It does what it implies in truth. In the event of a Tie, all the tieing rows are returned. In this case, where the value from ROW_NUMBER is 1, @Nanaki.

Gordon Linoff · Accepted Answer · 2019-01-11 12:12:09Z

1

You want the highest date for each IdArt/IdAdr combination. Window functions are tempting, but the most efficient method is often a correlated subquery.

Your query is only selecting from T_PriceData, so the rest of the query (the joins and group by) do not seem necessary -- unless the joins are filtering the data which seems unlikely because the joins are to reference tables.

So I would recommend:

SELECT P.IdArt, P.IdAdr, P.gDate, P.Price
FROM dbo.T_PriceData P 
WHERE P.gDate = (SELECT MAX(P2.gDate)
                 FROM dbo.T_PriceData P2
                 WHERE P2.IdArt = P.IdArt AND
                       P2.IAdr = P.IdAdr
                );

For performance you want indexes on (IdArt, IdAdr, gDate).

answered Jan 11, 2019 at 12:12

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

StepUp · Accepted Answer · 2019-01-11 08:31:52Z

0

You can use ROW_Number():

SELECT 
q.IdArt 
, q.IdArt
, q.IdADr   
, q.gDate
, q.Price
FROM (
SELECT 
  t.IdArt
, t.IdADr   
, t.gDate
, t.Price
, ROW_NUMBER() OVER (PARTITION BY t.IdArt, t.IdADr ORDER BY t.gDate DESC) rn
FROM dbo.T_PriceData t
) q
WHERE q.rn = 1

edited Jan 11, 2019 at 8:31

answered Jan 11, 2019 at 8:12

StepUp

38.5k16 gold badges95 silver badges159 bronze badges

Collectives™ on Stack Overflow

Selecting rows with highest date

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related