Sort records based on string

Question

Please consider the table below

Id           F1          F2          
---------------------------
1            Nima        a
2            Eli         a
3            Arian       a
4            Ava         b
5            Arsha       b
6            Rozhan      c
7            Zhina       c

I want to display records by sorting COLUMN F2 to display one record from each string category (a,b,c) in order

Id           F1          F2          
---------------------------
1            Nima        a
5            Arsha       b
6            Rozhan      c
2            Eli         a
4            Ava         b
7            Zhina       c
3            Arian       a

NOTE: a,b,c could be anything... it should take one record from one entry and then 2nd from 2nd entry.

I have used join, or group by records but no success.

Answer 1

You can use window functions in the order by:

select t.*
from t
order by row_number() over (partition by f2 order by id),
         f2;

The row_number() function (as used above) assigns a sequential number starting with 1 to each value of f2.

In older versions of MySQL, you can use a correlated subquery instead:

order by (select count(*) from t t2 where t2.f2 = t.f2 and t2.id <= t.id),
         f2;

For performance, you want an index on (f2, id).

Answer 2

MySQL version 5.7 – Syed Saqlain

SELECT id, f1, f2
FROM ( SELECT t1.id, t1.f1, t1.f2, COUNT(*) cnt
       FROM test t1
       JOIN test t2 ON t1.f2 = t2.f2 AND t1.id >= t2.id
       GROUP BY t1.id, t1.f1, t1.f2 ) t3
ORDER BY cnt, f2;

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=8138bd9ab5be36ba534a258d20b2e555

Answer 3

ROW_NUMBER() alternative for lower version of MYSQL. This query will work for version 5.5, 5.6 & 5.7.

-- MySQL (v5.7)
SELECT t.id, t.f1, t.f2
FROM (SELECT @row_no:=CASE WHEN @db_names=d.f2 THEN @row_no+1 ELSE 1 END AS row_number
           , @db_names:= d.f2
           , d.f2
           , d.f1
           , d.id
      FROM test d,
      (SELECT @row_no := 0,@db_names:='') x
      ORDER BY d.f2) t
ORDER BY t.row_number, t.f2

Please check from url https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=02dbb0086a6dd7c926d55a690bffbd06