So i have this table:

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.

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

CodePudding user response：

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.

CodePudding user response：

In order to solve this problem, you need to look for the maximum value in the order field, grouped by req_num.

If your version is MySQL 8.0, you can use the ROW_NUMBER window function to assign an ordinal value to that column in a descendent way, so that your max value has ranking = 1. Afterwards you check with an IF statement if your column has the value 1 or not, and replace with 'last' and 'not_last' accordingly.

SELECT tab.req_num,
       tab.status,
       tab.order,
       IF(max_values.max_order, 'last', 'not_last') AS last_req
FROM      tab 
LEFT JOIN (SELECT req_num,
                  MAX(order) AS max_order
           FROM tab
           GROUP BY req_num                ) max_values
       ON tab.req_num = max_values.req_num
      AND tab.order = max_values.max_order

Demo here.

If your version is MySQL 5.X, you can first use the MAX aggregation function in a subquery containing only req_num and order, by grouping on the former field and aggregating on the latter one. Then you can LEFT JOIN the original table with this result set, and the order values who will have assigned NULL will be the ones that are 'not_last'.

SELECT tab.req_num,
       tab.status,
       tab.order,
       IF(max_values.max_order, 'last', 'not_last') AS last_req
FROM      tab 
LEFT JOIN (SELECT req_num_,
                  MAX(order) AS max_order
           FROM tab
           GROUP BY req_num               ) max_values
       ON tab.req_num = max_values.req_num
      AND tab.order = max_values.max_order

Demo here.