Select order by round table?-CodePudding

I have a table A like this:

WcsID   Single_long, Single_short, Single_tag
1001    100          100           A
1002    100          100           A
1003    100          100           A
1004    100          100           B
1005    100          100           B
1006    100          100           B
1007    100          100           C
1008    100          100           C

Is there a good SQL statement to select round on tag? That means I need A-B-C as a round, in above example there are 3 rounds. I need to order by round first, then WcsID in that round, result should be like this:

-- round 1
1001        A
1004        B
1007        C
-- round 2
1002        A
1005        B
1008        C
-- round 3
1003        A
1006        B

CodePudding user response：

You can do something like this:

SELECT sorted.wcsID, sorted.single_tag,
       sorted.sort AS currentRound
FROM (
    SELECT wcsID, single_tag, 
        RANK() OVER (PARTITION BY single_tag ORDER BY wcsID) AS sort
    FROM a
) AS sorted
ORDER BY sorted.sort, sorted.single_tag;

The RANK function is really good in your situation for such sorting since it allows to both order by that what you are naming "round" and also by the wcsID. Furthermore, you can use it in a subquery in order to select also the "round" you've just created within your result set. To make sure it's also sorted by the single_tag, you can just add it at the end of the main query.

CodePudding user response：

you can try this:

CREATE TABLE test(col1 int, col2 varchar(10), col3 varchar(10), col4 varchar(10));
INSERT INTO test(col1, col2, col3, col4)
VALUES (1001, 100, 100, 'A'),(1002, 100, 100, 'A'),(1003, 100, 100, 'A'),(1004, 100, 100, 'B'),(1005, 100, 100, 'B'),(1006, 100, 100, 'B'),(1007, 100, 100, 'C'),(1008, 100, 100, 'C');

SELECT *
FROM (
    SELECT col1, col2, col3, col4
        , RANK() OVER (PARTITION BY Col4 ORDER BY Col1 ) rank 
    FROM test
) A
ORDER BY rank;