up vote 2 down vote favorite

I'm trying to write a query in PostgreSQL and I'm getting a little frustrated because it works in other database engines. I need to select the top 5 users from a given joins table like this:

SELECT users.*, 
       COUNT(deals.id) AS num_deals 
FROM users, deals 
WHERE deals.users_id = users.id 
GROUP BY users.id 
ORDER BY num_deals LIMIT 5;

I need the top 5 users. This code works in sqlite, mysql, etc, yet PostgreSQL refuses to select additional fields that aren't used in aggregate functions. I'm getting the following error:

PGError: ERROR:  column "users.id" must appear in the GROUP BY clause or be used in an aggregate function

How can I do this in PostgreSQL??

share|improve this question
100% accept rate
2  
I do believe it works in MySQL and SQLite but the "etc" is wrong. No other database allows this. Those are the only two. – a_horse_with_no_name Jan 21 '11 at 8:02
1  
Actually, assuming that users.id is a PRIMARY KEY, it is not wrong. (Though MySQL for example does it both when it's right and when it's wrong). PostgreSQL 9.1 will support running this query the way it is written - since the GROUP BY is on the PRIMARY KEY, we can infer that all the other columns are functionally dependent on it. – Magnus Hagander Jan 21 '11 at 9:58
@Magnus: I know that 9.1 will support this, but 9.1 is currently not available – a_horse_with_no_name Jan 21 '11 at 14:26
@horse: absolutely true. But the statement that they are wrong is partially (though only partially) incorrect. – Magnus Hagander Jan 21 '11 at 15:29
@MagnusHagander: Do you know why a PRIMARY KEY is required and not mere UNIQU-ness? I cannot imagine a case where uniqueness would not be good enough. – A.H. Mar 31 '12 at 22:33
show 1 more comment
feedback

3 Answers

up vote 7 down vote accepted

You could try: 

SELECT users.*, a.num_deals FROM users, (
    SELECT deal.id as dealid, COUNT(deals.id) AS num_deals 
    FROM deals 
    GROUP BY deal.id
) a where users.id = a.dealid
ORDER BY a.num_deals DESC
LIMIT 5
share|improve this answer
+1 for a cross-dbms solution. But the comma after users in the first line is wrong and you should order by num_deals DESC – a_horse_with_no_name Jan 21 '11 at 8:07
can't you remove the reference to the 'users' table in the subquery? that way it will only look at each table once. – araqnid Jan 21 '11 at 11:25
@a_horse_with_no_name: You're correct about the ORDER BY. ...and at first I thought you were correct about the comma in the first line, but I think it is actually correct (it's separating the users table from the subquery/a table) – Gerrat Jan 21 '11 at 13:56
@araqnid: I've implemented your suggestion. Thanks. – Gerrat Jan 21 '11 at 14:02
@Gerrat: ah, you are right about the comma. I'm not used to that old-style joins, I always use JOIN ... ON syntax. Sorry for the noise – a_horse_with_no_name Jan 21 '11 at 14:24
show 1 more comment
feedback
up vote 1 down vote

One other solution that works is to use all attributes implicitly in GROUP BY

Thus following will be final query

SELECT users.*, 
       COUNT(deals.id) AS num_deals 
FROM users, deals 
WHERE deals.users_id = users.id 
GROUP BY users.id, users.name, users.attrib1, ..., users.attribN
ORDER BY num_deals LIMIT 5;

If you are using framework like rails then you can implement this easily with Model.column_names function.

share|improve this answer
feedback
up vote 1 down vote

Assuming that users.id IS a PK, then you can either

wait for 9.1

group by all fields

use an aggregate (i.e. max() ) on all fields

share|improve this answer
with use max() - it low speed – satels Feb 22 '12 at 13:02
The use distinct on: select distinct on (col1, col2) col1, col2, col3, col4 from yada; – Scott Marlowe Feb 23 '12 at 16:15
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.