0

I would like to get the most efficient query to give me all the rows that are distinct on one field (ID) but are identical on another field (validity).

Let's set a concrete example: you have a table of meds, each having an ID, some values and a validity date. Of course, in the ideal world each single ID is an individual meds; unfortunately, in reality this is not the case as you might have two variants of the same medicine, with the same ID, some different values in the middle of the table and a validity date again the same. In this real-world example, you want to retrieve all the lines in which the medicine is "still valid" (for whatever definition of still, out of the scope of my question).

Consider the following table:

# ID # VALUE # VALIDITY
# 1  # foo   # 2018/01/10
# 2  # bar   # 2018/01/03
# 2  # flo   # 2018/01/10
# 2  # duv   # 2018/01/10
# 3  # pas   # 2018/01/10
# 4  # cip   # 2018/01/08
# 4  # mao   # 2018/01/10

I'd like the query to return the following lines:

# ID # VALUE # VALIDITY
# 1  # foo   # 2018/01/10
# 2  # flo   # 2018/01/10
# 2  # duv   # 2018/01/10
# 3  # pas   # 2018/01/10
# 4  # mao   # 2018/01/10

notice that (2, bar) and (4, cip) are both "outdated" by (2, flo; 2, duv; 4, cip) respectively.

You can imagine that this is a rather trivial exercise that you can solve using an INNER JOIN:

SELECT * FROM TABLE T
INNER JOIN (SELECT ID, MAX(VALIDITY) FROM T GROUP BY ID) AS TT
ON T.ID = TT.ID AND T.VALIDITY = TT.VALIDITY

However, I noticed that this query becomes quite expensive (essentially there are two full scans, plus an additional group by. I am not sure whether there is a less expensive way to do this (I tried with DISTINCT ON but that will kill the two entries with ID = 2). Possibly, recommend also indexes that will work on improve this use case.

Thank you so much!

  • 1
    Another method would be to use DENSE_RANK(): select id, value, validity from (select *, dense_rank() over (partition by id order by validity desc) as rnk from t) as r where rnk = 1; – ypercubeᵀᴹ Feb 19 '18 at 18:29
  • Or a not exists subquery: select * from t as a where not exists (select 1 from t as b where a.id = b.id and a.id < b.id) ; – ypercubeᵀᴹ Feb 19 '18 at 18:32
  • An index on (id, validity) would be the immediately obvious index. Did you have such an index when you tested your GROUP BY - MAX version? – ypercubeᵀᴹ Feb 19 '18 at 18:34
  • Hello @ypercubeᵀᴹ, thank you for your answer. Yes I had that index but only the index on (id) only was preferred (according to explain). – Fabiano Francesconi Feb 20 '18 at 8:45
  • @ypercubeᵀᴹ I assume that in your second comment you meant and a.validity < b.validity). Correct? – Fabiano Francesconi Feb 20 '18 at 10:42
1

The same result als ypercube, but I find a simple max windowing function easier to understand than dense ranks and such.

select mm.id, mm.value, mm.validity from (
   select m.*, max(m.validity) over (partition by m.id) maxval
      from meds m ) mm
   where mm.validity = mm.maxval;

see http://sqlfiddle.com/#!17/e478c/4 for the results.

  • I actually like more the max windowing function because you don't expose the rank in the outermost query. – Fabiano Francesconi Feb 20 '18 at 10:38
  • But you could, if you wanted, but because of the "mm.validity = mm.maxval" it wouldn't add anything new. ;-) If you think this is the answer, can you please accept it? – Gerard H. Pille Feb 20 '18 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.