Slashdot Query

From: Chris Bitmead <chris(dot)bitmead(at)bigfoot(dot)com>
To: pgsql-sql(at)hub(dot)org
Subject: Slashdot Query
Date: 1999-06-04 03:18:53
Message-ID: 3757459C.F0E63B5E@bigfoot.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


I want to do a query that is like the icons on the top of
http://slashdot.org. That is I want to select the 5 most recent distinct
categories from a bunch of stories. So I have...

SELECT DISTINCT category.oid, category.title, category.image FROM story,
category* WHERE story.category = category.oid AND story.approved ORDER
BY datetime DESC LIMIT 5;

The trouble is it doesn't return distinct results, but rather it returns
duplicates. Any ideas how I can do this query?

I thought there might be some sub-select solution, but I don't really
understand subselects. I thought of,

SELECT distinct * from (SELECT category.oid, category.title,
category.image FROM story, category* WHERE story.category = category.oid
AND story.approved ORDER BY datetime DESC )LIMIT 5;

But that doesn't work in any shape or form.

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message nathan 1999-06-04 05:17:23 Embedded SQL in 'C'
Previous Message Tim Perdue 1999-06-04 01:47:30 Group By Dilemma