John4711

ranked_messages, cte, row_number

May 25th, 2021 (edited)
99
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 0.60 KB | None | 0 0
  1. WITH ranked_messages AS (
  2.   SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  3.   FROM messages AS m
  4. )
  5. SELECT * FROM ranked_messages WHERE rn = 1;
  6.  
  7. /* last row by group */
  8. WITH ranked_messages AS (
  9.   SELECT b.*, ROW_NUMBER() OVER (PARTITION BY i ORDER BY ts DESC) AS rn
  10.   FROM e0407 a JOIN rdns2 b USING(i)
  11. )
  12. SELECT * FROM ranked_messages -- WHERE rn = 1;
  13.  
  14.  
  15.  
  16. /* last 10 rows by group */
  17.  
  18. WITH ranked_messages AS (
  19.   SELECT b.*, ROW_NUMBER() OVER (PARTITION BY i ORDER BY ts DESC) AS rn
  20.   FROM e0407 a JOIN tab2 b USING(i)
  21. )
  22. SELECT * FROM ranked_messages  WHERE rn <=10;
Add Comment
Please, Sign In to add comment