Advertisement
cdsatrian

list data at last date of month

Aug 14th, 2022
897
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE IF EXISTS test.test_data;
  2. CREATE TABLE test.test_data(
  3.     id_user INT2,
  4.     thn INT2,
  5.     bln INT2,
  6.     tgl INT2,
  7.     nilai INT
  8. );
  9.  
  10. DELETE FROM test.test_data;
  11. INSERT INTO test.test_data(id_user,thn,bln,tgl,nilai)
  12. VALUES
  13. (1,2022,10,30,12),
  14. (1,2022,10,31,15),
  15. (1,2022,11,1,1),
  16. (1,2022,11,2,1),
  17. (1,2022,11,3,1),
  18. (1,2022,11,4,1),
  19. (1,2022,11,5,1),
  20. (1,2022,11,6,2),
  21. (1,2022,11,7,2),
  22. (1,2022,11,8,2),
  23. (1,2022,11,9,2),
  24. (1,2022,11,10,2),
  25. (1,2022,11,11,2),
  26. (1,2022,11,12,4),
  27. (1,2022,11,13,4),
  28. (1,2022,11,14,6),
  29. (1,2022,11,15,7),
  30. (1,2022,11,16,9),
  31. (1,2022,11,17,9),
  32. (1,2022,11,18,9),
  33. (1,2022,11,19,11),
  34. (1,2022,11,20,14),
  35. (1,2022,11,21,14),
  36. (1,2022,11,22,14),
  37. (1,2022,11,23,15),
  38. (1,2022,11,24,16),
  39. (1,2022,11,25,17),
  40. (1,2022,11,26,17),
  41. (1,2022,11,27,18),
  42. (1,2022,11,28,20),
  43. (1,2022,11,29,23),
  44. (1,2022,11,30,24),
  45. (1,2022,12,1,1),
  46. (1,2022,12,2,1),
  47. (1,2022,12,3,2),
  48. (1,2022,12,4,3),
  49. (4,2022,10,30,10),
  50. (4,2022,10,31,12),
  51. (4,2022,11,1,1),
  52. (4,2022,11,2,2),
  53. (4,2022,11,3,2),
  54. (4,2022,11,4,3),
  55. (4,2022,11,5,3),
  56. (4,2022,11,6,4),
  57. (4,2022,11,7,5),
  58. (4,2022,11,8,7),
  59. (4,2022,11,9,7),
  60. (4,2022,11,10,8),
  61. (4,2022,11,11,9),
  62. (4,2022,11,12,10),
  63. (4,2022,11,13,14),
  64. (4,2022,11,14,16),
  65. (4,2022,11,15,17),
  66. (4,2022,11,16,19),
  67. (4,2022,11,17,19),
  68. (4,2022,11,18,19),
  69. (4,2022,11,19,21),
  70. (4,2022,11,20,22),
  71. (4,2022,11,21,22),
  72. (4,2022,11,22,23),
  73. (4,2022,11,23,24),
  74. (4,2022,11,24,25),
  75. (4,2022,11,25,26),
  76. (4,2022,11,26,27),
  77. (4,2022,11,27,27),
  78. (4,2022,11,28,28),
  79. (4,2022,11,29,28),
  80. (4,2022,11,30,29),
  81. (4,2022,12,1,1),
  82. (4,2022,12,2,2),
  83. (4,2022,12,3,4),
  84. (4,2022,12,4,5);
  85.  
  86. SELECT
  87.     x.id_user,x.thn,x.bln,x.tgl,nilai
  88. FROM
  89. (
  90.     SELECT
  91.         id_user,thn,bln,tgl,nilai,
  92.         RANK() OVER(
  93.             PARTITION BY id_user,thn,bln
  94.             ORDER BY thn DESC, bln DESC, nilai DESC
  95.         ) AS ranking
  96.     FROM test.test_data
  97. ) x
  98. WHERE
  99.     x.ranking=1
  100. ORDER BY
  101.     x.id_user,x.thn,x.bln,x.tgl
  102. ;
  103.  
  104. id_user |   thn     |  bln  |  tgl  |  nilai   
  105.     1   |   2022    |   10  |   31  |   15
  106.     1   |   2022    |   11  |   30  |   24
  107.     1   |   2022    |   12  |   4   |   3  
  108.     4   |   2022    |   10  |   31  |   12
  109.     4   |   2022    |   11  |   30  |   29
  110.     4   |   2022    |   12  |   4   |   5  
  111.    
  112.    
  113.    
  114. WITH  x AS (
  115.     SELECT
  116.         id_user,thn,bln,tgl,nilai,
  117.         RANK() OVER(
  118.             PARTITION BY id_user,thn,bln
  119.             ORDER BY thn DESC, bln DESC, nilai DESC
  120.         ) AS ranking
  121.     FROM test.test_data
  122. )
  123. SELECT
  124.     x.id_user,x.thn,x.bln,x.tgl,nilai
  125. FROM x
  126. WHERE
  127.     x.ranking=1
  128. ORDER BY
  129.     x.id_user,x.thn,x.bln,x.tgl
  130. ;
  131.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement