Re: Help with SQL - Mailing list pgsql-sql

From Oliver Elphick
Subject Re: Help with SQL
Date
Msg-id 1034800739.22821.105.camel@linda
Whole thread Raw
In response to Help with SQL  ("Eric L. Blevins" <eblevins@insight.rr.com>)
List pgsql-sql
On Wed, 2002-10-16 at 19:26, Eric L. Blevins wrote:
> I've got 2 SQL statements I would like to combine into one.
...
> statement 1: SELECT  uid, count(uid)  FROM triangulated WHERE uid != 'anonymus' AND uid
> != 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10;
...
> statement 2: SELECT  uid, count(uid)  FROM points WHERE uid != 'anonymus' AND uid !=
> 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10;
...
> what I want to do is have one statement that returns something like this:
> uid    |    count1    | count2
> eblevins    1179    23595
> DaClyde    398    11031
> Drew    30    104
> zombiechick    3    159
> 
> So everything is ordered like statement 1 but includes the count(uid) from the points DB like statement 2 returns

SELECT * FROM  (SELECT uid, count(uid) AS count1     FROM triangulated    WHERE uid != 'anonymus' AND          uid !=
'anonymous'AND          uid != ''    GROUP BY uid) AS c1  LEFT JOIN  (SELECT uid, count(uid) AS count2     FROM points
 WHERE uid != 'anonymus' AND          uid != 'anonymous' AND          uid != ''    GROUP BY uid) AS c2 ORDER BY count1
DESCLIMIT 10;
 

(Apologies for syntax errors, if any - I haven't tried it out,)

I used LEFT JOIN because you are ordering by count1, so you probably
won't want any rows where count1 is null.  If the total of rows from
subselect c1 was likely to be less than 10, you might want to do a FULL
JOIN and order by count1, count2.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "But be ye doers of the word, and not hearers only,      deceiving your own
selves."             James 1:22 
 



pgsql-sql by date:

Previous
From: "Eric L. Blevins"
Date:
Subject: Help with SQL
Next
From: "Eric L. Blevins"
Date:
Subject: Re: Help with SQL