Select Multiple Columns Group By One Column Order By Count

I have the following data set in Oracle: c1 c2 c3 1A2 cat black 1G2 dog red B11 frog green 1G2 girl red Trying to get the following result. Basically am trying to get

Solution 1:

try this:

This will work in almost all RDBMSs

SELECT  t.c1, t.c2,t.c3 
FROM    your_table t
JOIN(
        select c1,count(*) as cnt from your_table
        group by c1 )a
ON      a.c1=t.c1
ORDER BY a.cnt desc,t.c2,t.c3

SQL Fiddle Demo

Solution 2:

Getting all duplicates from column c1 can be achivied by simple ordering:

SELECT * FROM <table> ORDER BY C1 ASC

Solution 3:

select c1,c2,c3
from tbl
order by count(*) over (partition by c1) desc