Select multiple columns in the same column order in the account

Question

I have the following dataset in Oracle:

c1 c2 c3 1A2 cat black 1G2 dog red B11 frog green 1G2 girl red 

Trying to get the following result. Basically I am trying to get rows with duplicate c1 first.

 c1 c2 c3 1G2 dog red 1G2 girl red B11 frog green 1A2 cat black 

It starts with choosing c1 from the group t1 in order of c1 according to the count (*) desc, but not how to continue.

Answer 1

try the following:

This will work in almost all RDBMS

 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

Answer 2

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

 SELECT * FROM <table> ORDER BY C1 ASC 

Answer 3

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