Mysql – How to Sort a Table by Multiple Columns in SQL: Partitioning by ISIN and Sorting by Match_ID and Ref_Number

To sort the output based on isin, match_id and ref_number in ascending order, you can use the following query:

SELECT match_id, isin, start_date, ref_number
FROM employee
ORDER BY isin ASC, match_id ASC, ref_number ASC;

If you want to sort the output in descending order, you can use the following query:

SELECT match_id, isin, start_date, ref_number
FROM employee
ORDER BY isin DESC, match_id DESC, ref_number DESC;

To partition the data by isin and then sort by match_id and ref_number for each partition, use the PARTITION BY in your ORDER BY expression.

Here’s an example of a query that orders data based on isin first and then based on the match_id and ref_number of each partition:

SELECT match_id, isin, start_date, ref_number
FROM (
    SELECT match_id, isin, start_date, ref_number,
           ROW_NUMBER() OVER (PARTITION BY isin ORDER BY match_id, ref_number) AS rn
    FROM employee
) AS emp
ORDER BY isin ASC, rn ASC;

ROW_NUMBER() function is used to assign a unique row number to each row within a partition based on the order specified in the ORDER BY.

PARTITION BY clause is used to divide the rows into partitions based on the isin. Within each partition, the rows are sorted by match_id and ref_number. Finally, the outer query sorts the rows by isin and then by the unique row number assigned to each row within the partition using ORDER BY.

Click here to learn more about SQL order by keyword.

Click here to learn more about SQL partition by.