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!

6
  • 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; Commented Feb 19, 2018 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) ; Commented Feb 19, 2018 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? Commented Feb 19, 2018 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). Commented Feb 20, 2018 at 8:45
  • @ypercubeᵀᴹ I assume that in your second comment you meant and a.validity < b.validity). Correct? Commented Feb 20, 2018 at 10:42

1 Answer 1

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.

2
  • I actually like more the max windowing function because you don't expose the rank in the outermost query. Commented Feb 20, 2018 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? Commented Feb 20, 2018 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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