Select order by round table?

May 21, 2022

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

>Solution :

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;