MySQL: Enumerate and count

Question

I have two tables, table1 and table2.

Example of the table1 table.

^ invoice ^ valid ^ 
| 10       | yes   | 
| 11       | yes   | 
| 12       | no    | 

Example of the table2 table

^ invoice ^ detail ^ 
| 10       | A      | 
| 10       | C      | 
| 10       | F      | 
| 11       | A      | 
| 11       | F      |
| 10       | E      | 
| 12       | A      | 

Want to select from table 2 all rows that:

• Have a valid invoice in table 1

And enumerate:

• the detail for each invoice
• the invoice

Here the desired result

^ invoice ^ detail ^ ordination ^ ordinationb      ^ 
| 10       | A      |     1       |       1         | 
| 10       | C      |     2       |       1         |  
| 10       | F      |     3       |       1         |  
| 11       | A      |     1       |       2         |  
| 11       | F      |     2       |       2         | 
| 10       | E      |     4       |       1         | 

The sentence should valid for use in phpMyAdmin 4.8.4

Answer 1

Here is the MySQL 8+ way of doing this:

SELECT
    t2.Invoice,
    t2.`lines`,
    ROW_NUMBER() OVER (PARTITION BY t2.Invoice ORDER BY t2.`lines`) line_order,
    DENSE_RANK() OVER (ORDER BY t2.Invoice) ordination
FROM table2 t2
WHERE EXISTS (SELECT 1 FROM table1 t1 WHERE t1.Invoice = t2.Invoice AND t1.valid = 'yes');

Demo

If you are using a version of MySQL earlier than 8, then you might have to resort to using session variables. This can lead to an ugly query. If you have a long term need for queries like this one, then I recommending upgrading to MySQL 8+.

Edit:

It just dawned on me that we can use correlated subqueries to simulate both your ROW_NUMBER and DENSE_RANK requirements. Here is one way to do this query in MySQL 5.7 or earlier:

SELECT
    t2.Invoice,
    t2.detail,
    (SELECT COUNT(*) FROM table2 t
    WHERE t.Invoice = t2.Invoice AND t.detail <= t2.detail) ordination,
    t.dr AS ordinationb
FROM table2 t2
INNER JOIN
(
    SELECT DISTINCT
        t2.Invoice,
        (SELECT COUNT(*)
         FROM (SELECT DISTINCT Invoice FROM table2) t
         WHERE t.Invoice <= t2.Invoice) dr
    FROM table2 t2
) t
    ON t.Invoice = t2.Invoice
WHERE EXISTS (SELECT 1 FROM table1 t1 WHERE t1.Invoice = t2.Invoice AND t1.valid = 'yes')
ORDER BY
    t2.Invoice,
    t2.detail;

Demo