Get the top value row of a table without id

Issue

So i have this table:

req_num number status order
1254 5 7 1
1254 4 7 2
1254 6 7 3
1246 7 8 1
1246 5 8 2
1246 3 8 3
1253 1 9 1
1253 4 7 2
1253 7 4 3
1321 7 4 1
1321 8 4 2
1321 7 4 3
1321 8 4 4

and i need to know if theres a way to generate a query that get a column like this based on the highest value in the "order" column.

req_num number status order last_req
1254 5 7 1 not_last
1254 4 7 2 not_last
1254 6 7 3 last
1246 7 8 1 not_last
1246 5 8 2 not_last
1246 3 8 3 last
1253 1 9 1 not_last
1253 4 7 2 not_last
1253 7 4 3 last
1321 7 4 1 not_last
1321 8 4 2 not_last
1321 7 4 3 not_last
1321 8 4 4 last

this table doesnt have any ids and i can’t do inserts on it, any ideas?

Solution

You can use window functions to accomplish this – you can partition your data by req_num and then take the item in each partition with the highest order.

The docs: https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html

The result will be something like (untested):

SELECT rec_num, number, status, LAST_VAL(order) OVER w AS 'last',
FROM my_table
WINDOW w AS (PARTITION BY req_num ORDER BY order);

That won’t give you exactly what you want as all the rows will now include the highest order num for that rec_num. But you can then easily compare the order and last columns to create your last and not_last identifiers.

SELECT rec_num, number, status, LAST_VAL(order) OVER w AS 'last',
    IF(`order` = `last`, 'last', 'not_last') AS last_req
FROM my_table
WINDOW w AS (PARTITION BY req_num ORDER BY order);

The window functions are remarkably powerful for situations like this.

Answered By – Jerry

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published