CODEWITHSUNDEEP
sqlpostgresql
gr8linux
gr8linux

Reputation: 3

How to order the query result by count() of field in postgresql without group by

I have simple database and I thing my answer should be so simple but I can't find my answer the db is look like:

-----------------
name | ip
-----------------
nick |192.168.1.10
john |192.168.1.1
john |192.168.1.2
john |192.168.1.3
lucy |192.168.10.1
lucy |192.168.10.2

I need a query that return all the rows but the result sorted by count(ip) per name and the result of above list should be something like :

------------------
name |ip
------------------
nick |192.168.1.10
lucy |192.168.10.1
lucy |192.168.10.2
john |192.168.1.1
john |192.168.1.2
john |192.168.1.3

Upvotes: 0

Views: 168

Answers (4)

user330315
user330315

Reputation:

This should be faster than a self join on the table.

select name, ip
from (
  select name,
         ip,
         count(*) over (partition by name) as cnt
  from the_table
) t
order by cnt;

Upvotes: 0

Hamidreza
Hamidreza

Reputation: 3128

try this query:

SELECT t1.* 
FROM   table1 t1 
       INNER JOIN (SELECT Count(name) counter, 
                          name 
                   FROM   table1 
                   GROUP  BY name)t2 
               ON t1.name = t2.name 
ORDER  BY counter;

SQL Fiddle

Upvotes: 1

Subash
Subash

Reputation: 1

SELECT NAME,IP,
       COUNT(NAME) KEEP (DENSE_RANK LAST ORDER BY NAME) 
               OVER (PARTITION BY NAME) "Answer"
FROM PSQL
ORDER BY 3

Ans By Subash M

Need to tell correct answer Y/N?

Upvotes: 0

Diaton
Diaton

Reputation: 151

Here a SQL Fiddle

Try this statement.

SELECT * FROM tblSAMPLE ORDER BY name DESC,ip

Upvotes: 1

Related Questions