Row_Number() Window Function in Former MariaDB versions

Question

does anyone know how to write this SQL query in a version where window functions are not supported?

SELECT *,
       ROW_NUMBER() OVER(PARTITION BY tr.ticketId ORDER BY tr.time DESC) AS row_num
  FROM tickets AS t
  JOIN ticket_responses AS tr
    ON tr.ticketId = t.id
 WHERE row_num = 1

I've found MYSQL 5.7 Getting the row number but I don't know how to use PARTITION in this way

Does this answer your question? [MYSQL 5.7 Getting the row number](https://stackoverflow.com/questions/54239851/mysql-5-7-getting-the-row-number) — Dai, Dec 31 '20 at 10:21

Barbaros Özhan · Answer 1 · 2020-12-31T12:26:15.307

0

You can add a new iterator variables initialized as zero such as

SELECT id, name, ticketId, time
  FROM
  (
  SELECT @row_num := IF(@id = tr.ticketId, @row_num + 1, 1) AS row_num,
         @id := tr.ticketId, tr.*
    FROM (SELECT @id := 0, @row_num := 0, tr.*, t.name 
            FROM ticket_responses AS tr
            JOIN tickets AS t
              ON tr.ticketId = t.id
           ORDER BY ticketId, time DESC) AS tr
  ) AS tt  
  WHERE tt.row_num = 1

where IF(@id = tr.ticketId, @rn + 1, 1) part regulates the partitioning along with
@id := tr.ticketId while generating iterative integers

Demo

edited Dec 31 '20 at 12:26

answered Dec 31 '20 at 10:29

Barbaros Özhan

59,113
10
31
55

I'm tried this code and it's throwing error "Duplicate name 'column'", so I changed code to `...SELECT @row_num := IF(@id = tr.ticketId, @rn + 1, 1) AS row_num, @id := tr.ticketId, t.*, tr.author as lastResponseAuthor, tr.id as lastResponseId, tr.type as lastResponseType, tr.time as lastResponseTime` and result is https://i.imgur.com/9aUOTNQ.png, but i need https://i.imgur.com/AJ2ejrd.png Sorry, it's my fault, I should have sent these screens at the beginning. – Milan Turyna Dec 31 '20 at 11:21
Now it's returning 4 lines as it should, but last response is not last response, maybe I just wrote it wrong, but it doesn't matter, I found this solution: `SELECT * from tickets as t LEFT JOIN (SELECT ticketId, max(time) as time FROM ticket_responses GROUP BY ticketId) as last_responses ON t.id = last_responses.ticketId LEFT JOIN ticket_responses as tr ON t.id = tr.ticketId AND last_responses.time = tr.time;` and it's working too. Thanks for help. – Milan Turyna Dec 31 '20 at 11:41

score 0 · Answer 2 · answered Dec 31 '20 at 12:25

As a starter: your query is not valid SQL. You can use a window function in the where clause, you need a subquery:

select *
from (
    select *,
        row_number() over(partition by tr.ticketid order by tr.time desc) as row_num
    from tickets as t
    join ticket_responses as tr on tr.ticketid = t.id
) t
where row_num = 1

You could do this using variables, but I would not suggest that. MySQL variables have pitfalls, and their future deprecation was announced in MySQL 8.0. I would recommend a correlated subquery:

select *,
    (
        select count(*) 
        from ticket_responses tr1 
        where tr1.ticketid = t.ticketid and tr1.time <= tr.time
    ) as row_num
from tickets as t
join ticket_responses as tr on tr.ticketid = t.id

There are a few assumptions here:

tickets / ticket_responses is a one-to-many relationship
tuples of (ticket_id, time) are unique in table ticket_responses

Performance might suffer against a large dataset. An index on ticket_responses(ticket_id, time) might help.

score 0 · Answer 3 · answered Dec 31 '20 at 12:45

Your query is not valid, because row_num is not defined for the where clause. In MariaDB, you would need a subquery or CTE. Let me just assume you oversimplified the query and you want the ticket_response with the most recent time.

I would suggest a correlated subquery:

SELECT *
FROM tickets t JOIN
     ticket_responses tr
     ON tr.ticketId = t.id
WHERE tr.time = (SELECT MIN(tr2.time)
                 FROM ticket_responses tr2
                 WHERE tr2.ticketId = tr.ticketId
                );

Note: This is not exactly the same. It is actually equivalent to rank() -- but if time is unique, it returns the same results.

If time may not be unique, then you need some way to separate the duplicates on time. If I assume that there is a unique id in ticket_responses, then:

SELECT *
FROM tickets t JOIN
     ticket_responses tr
     ON tr.ticketId = t.id
WHERE tr.ticket_responses_id = (SELECT tr2.ticket_responses_id
                                FROM ticket_responses tr2
                                WHERE tr2.ticketId = tr.ticketId
                                ORDER BY tr2.time DESC, tr2.ticket_responses_id DESC
                                LIMIT 1
                               );

Row_Number() Window Function in Former MariaDB versions

3 Answers3