Use LIMIT on two tables with an Inner Join

Question

The following SQL code works fine. It has the purpose of displaying all the products ordered by each user and sorting them by products in a descending manner.

The problems occur when I try to limit the display of customers and their related products, so when i use LIMIT 2. I tried to insert LIMIT 2 immediately after the ORDER BY line, but there is a problem: I only display one customer with its 2 purchases, so in total I only have 2 lines printed.

cursor.execute('''SELECT Client.client_name, Purchases.product 
                  FROM Client
                  INNER JOIN Purchases 
                  ON Client.client_name = Purchases.client_name
                  ORDER BY client_name;''')

I would like to print 2 lines for each customer. Where and how should I enter LIMIT 2? Can you show me please? (trying not to write a too complicated sql, because i understand little of Sql and i would like to try to keep the code as simple as possible)

In the case of the example below, I would like to print James, George, Sophie and get this output:

client_name | product   |
\------------+-----------+
James      | Hard Disk |
James      | Mouse     |
George     | Book      |
George     | Keyboard  |  
Sophie     | Mouse     |
Sophie     | Coffee    |

The two main tables I'm using are these:

CLIENT


client_name |  born  |
-----------+---------+
James      |   1988  |
George     |   1988  |
Sophie     |   1988  |

PURCHASES

client_name | product   |
------------+-----------+
James      | Hard Disk |
James      | Mouse     |
George     | Book      |
George     | Keyboard  |  
Sophie     | Mouse     |
Sophie     | Coffee    |
Harry      | Cd        |
Harry      | Book      |
Lily       | Mouse     |
Lily       | Desk      |

score 1 · Accepted Answer · answered Dec 21 '22 at 21:48

1

You can use the row_number window function to count assign a number to each product per client, and then have a condition on it:

SELECT   client_name, product
FROM     (SELECT     Client.client_name, 
                     Purchases.product,
                     ROW_NUMBER() OVER (PARTITION BY Client.client_name 
                                        ORDER BY Purchases.product DESC) AS rn
          FROM       Client
          INNER JOIN Purchases  ON Client.client_name = Purchases.client_name) t
WHERE    rn <= 2
ORDER BY client_name ASC

answered Dec 21 '22 at 21:48

Mureinik

297,002
52
306
350

They unfairly closed the question, although in my opinion they were wrong. It's not a duplicate. Anyway now I try your code and I accept your answer in case the code works. Wait a minute. Thank you – Dec 21 '22 at 21:56
It seems to work fine, thanks. I didn't understand one thing: why did you write two different ORDER BY (DESC and ASC)? Wasn't just the last ORDER BY client_name ASC enough? Thank you – Dec 21 '22 at 22:10
@Rob92 the `order by` at the end of the query orders the results by the client name. The `order by` in the `over` clause tells `row_number` how it should order the numbering (descending by product name, as per your requirements). – Mureinik Dec 21 '22 at 22:11
If I set ORDER BY as ASC in the over clause tells row_number, what will happen? Will what I asked for no longer work? Or is it just a matter of preference? – Dec 21 '22 at 22:14
@Rob92 you'll get the first two products instead of the last two products – Mureinik Dec 21 '22 at 22:15

Use LIMIT on two tables with an Inner Join

1 Answers1