Re: Help with SQL - Mailing list pgsql-sql
From | Eric L. Blevins |
---|---|
Subject | Re: Help with SQL |
Date | |
Msg-id | 00c301c27557$2c2cc010$0201a8c0@Gateway Whole thread Raw |
In response to | Help with SQL ("Eric L. Blevins" <eblevins@insight.rr.com>) |
List | pgsql-sql |
This is what I ended up with: SELECT c1.uid, count1, count2 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 on (c1.uid = c2.uid) ORDER BY count1 DESC LIMIT 10; I got the results I wanted! uid | count1 | count2 -------------+--------+--------eblevins | 1179 | 23595DaClyde | 398 | 11031Drew | 30 | 104zombiechick| 3 | 159 (4 rows) Thanks for your help! Eric L. Blevins ----- Original Message ----- From: "Oliver Elphick" <olly@lfix.co.uk> To: "Eric L. Blevins" <eblevins@insight.rr.com> Cc: <pgsql-sql@postgresql.org> Sent: Wednesday, October 16, 2002 4:38 PM Subject: Re: [SQL] Help with 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 DESC > LIMIT 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 > >