ROW_NUMBER  SQL. 18   
 


   ROW_NUMBER    SQL  .  18    :  ,    , , ,  ,  ,     .     SQLite,    .





 

ROW_NUMBER  SQL. 18   





  





  ROW_NUMBER


ROW_NUMBER   , ,         .

  :   ,    ,        .

   :          .




    


PARTITION BY   : , , ,   .

ORDER BY  OVER    :   ,   ,    .

    ,    ,     ,   .




  


1.        (#task-1)

2.      (#task-2)

3.     (#task-3)

4.        (#litres_trial_promo)

5.       (#litres_trial_promo)

6.     (#litres_trial_promo)

7.      (#litres_trial_promo)

8.     (#litres_trial_promo)

9.      (#litres_trial_promo)

10.      (#litres_trial_promo)

11.  ROW_NUMBER  RANK   (#litres_trial_promo)

12.     (#litres_trial_promo)

13.       (#litres_trial_promo)

14.     (#litres_trial_promo)

15.      (#litres_trial_promo)

16.      (#litres_trial_promo)

17.      (#litres_trial_promo)

18.      (#litres_trial_promo)




 





 1.       



 

         ,        .




CREATE TABLE orders(client_id INTEGER, order_id INTEGER, order_date TEXT);

INSERT INTO orders VALUES

(10,1,'2026-09-01'),(10,2,'2026-09-03'),(20,3,'2026-09-02'),(20,4,'2026-09-05');




SELECT client_id, order_id, ROW_NUMBER() OVER (

PARTITION BY client_id ORDER BY order_date DESC

) AS rn

FROM orders

ORDER BY client_id, rn;


 

[[10, 2, 1], [10, 1, 2], [20, 4, 1], [20, 3, 2]]


 

ROW_NUMBER     ,  PARTITION BY      .

     ,           .

ORDER BY      :        .


  

  PARTITION BY,     ,      .


 

   10   1?  ,     .

:  2,    2026-09-03  2026-09-01.




 2.     



 

       ,     .




CREATE TABLE orders(client_id INTEGER, order_id INTEGER, order_date TEXT);

INSERT INTO orders VALUES

(10,1,'2026-09-01'),(10,2,'2026-09-03'),(20,3,'2026-09-02'),(20,4,'2026-09-05');




WITH ranked AS (

SELECT client_id, order_id, order_date,

ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY order_date DESC) AS rn

FROM orders

)

SELECT client_id, order_id, order_date

FROM ranked

WHERE rn = 1

ORDER BY client_id;


 

[[10, 2, "2026-09-03"], [20, 4, "2026-09-05"]]


 

CTE   ,   SELECT   rn = 1   .

     ,           .

ORDER BY      :        .


  

 rn    SELECT  WHERE :       .


 

     CTE?  ,     .

:      rn,      .




 3.    



 

        ,      ,   .




CREATE TABLE sales(category TEXT, sku TEXT, revenue INTEGER);

INSERT INTO sales VALUES

('books','A',500),('books','B',700),('books','C',300),('apps','D',900),('apps','E',400);




SELECT category, sku, revenue,

ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS place

FROM sales

ORDER BY category, place;


 

[["apps", "D", 900, 1], ["apps", "E", 400, 2], ["books", "B", 700, 1], ["books", "A", 500, 2], ["books", "C", 300, 3]]


 

PARTITION BY category    ,  ORDER BY revenue DESC    .

     ,           .




  .


   .

   ,     (https://www.litres.ru/book/raznoe/row-number-v-sql-18-zadach-po-ranzhirovaniiu-74419827/)  .

      Visa, MasterCard, Maestro,    ,   ,     ,  PayPal, WebMoney, ., QIWI ,       .


