Selecting rows with highest date

问题: 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 JOI...

问题:

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.


回答1:

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);

回答2:

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).


回答3:

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
  • 发表于 2019-01-16 20:32
  • 阅读 ( 183 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除