How to show rows with duplicate values first in SQL without grouping-CodePudding

I have a Table like this I want to fetch all products, but I want to show products with the same PID first (without grouping them)

Sample Table

pid product
1   Red
3   Green
2   Blue
4   Violet
3   Magenta
1   Black

Expected Results

pid product
1   Red
1   Black
3   Green
3   Magenta
2   Blue
4   Violet

I tried this ORDER BY pid ASC it partially solves the problem but results appear like this

pid product
1   Red
1   Black
2   Blue (comes in between)
3   Green
3   Magenta
4   Violet

I want all products with more than one pid to come first sorted from top to bottom.

CodePudding user response：

From the comment I think your MySQL version doesn't support windows function so you could use:

SELECT
  t.pid,
  t.product,
            ( SELECT COUNT('pid') 
              FROM test_tbl ct 
              WHERE ct.pid = t.pid
            ) as counter
FROM
  test_tbl t
  order by counter desc,pid asc ;

Result:

pid   product   counter
1      Red        2
1      Black      2
3      Green      2
3      Magenta    2
2      Blue       1
4      Violet     1

Demo

CodePudding user response：

WITH cte AS (
    SELECT *, COUNT(*) OVER (PARTITION BY pid) cnt
    FROM datatable
)
SELECT * FROM cte ORDER BY cnt DESC