-2

Could you help me with a query I'm trying to do please? The idea is to select the latest created_date from each val1, val2 unique combination.

https://www.db-fiddle.com/f/fHc6MafduyibJdkLHe9cva/0

Expected result:

val1 val2 num1 num2 created_date
X A 33 333 2022-11-03
X B 66 666 2022-11-06
X C 88 888 2022-11-08
X D 99 999 2022-11-09
Y A 111 1111 2022-11-11

To the moderators. My question is different from the one below because I'm using 2 tables instead of just one, and that's the complex part for me. Please see fiddle link for more details. Get records with max value for each group of grouped SQL results

JohnP
  • 33
  • 6

1 Answers1

1

I would use the windowing function ROW_NUMBER() to avoid a self join:

SELECT *
FROM
  (SELECT d.val1,
          d.val2,
          s.created_date,
          s.num1,
          s.num2,
          ROW_NUMBER() OVER(PARTITION BY d.val1, d.val2
                            ORDER BY d.val1, d.val2, s.created_date DESC) AS row_num
   FROM scan AS s,
        dir AS d
   WHERE s.t2id=d.t2id) AS a
WHERE a.row_num=1;

https://www.db-fiddle.com/f/mj9UExg3bqeHegyVsDcxFA/0

Olli
  • 689
  • 1
  • 6
  • 13
  • 1
    Upvoted, but FWIW the `ORDER BY d.val1, d.val2` is redundant, since you're partitioning by those columns. You only need `ORDER BY s.created_date DESC`. – Bill Karwin Dec 03 '22 at 01:03
  • Yeah, you right.... its an old habit... sorry for that ;) – Olli Dec 03 '22 at 01:11
  • just noticed that I have MySQL v5.6. and seems that the "OVER" is not allowed. Could you help me with a solution for this please? Thanks in advance – JohnP Dec 03 '22 at 04:10