pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Slashdot Query
7+ messages / 3 participants
[nested] [flat]

* Slashdot Query
@ 1999-06-04 03:18  Chris Bitmead <chris.bitmead@bigfoot.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Chris Bitmead @ 1999-06-04 03:18 UTC (permalink / raw)
  To: pgsql-sql@hub.org


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.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Slashdot Query
@ 1999-06-04 14:21  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Chris Bitmead <chris.bitmead@bigfoot.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Tom Lane @ 1999-06-04 14:21 UTC (permalink / raw)
  To: Chris Bitmead <chris.bitmead@bigfoot.com>; +Cc: pgsql-sql@hub.org

Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> 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.

This is a known problem --- SELECT DISTINCT implies sorting by the
fields that are being "distincted" on, since the actual duplicate-
elimination relies on a uniq(1)-like adjacent-duplicates filter.
Forcing a sort by a different field breaks the duplicate eliminator.

It is not real clear what the correct behavior is, which is why
nothing's been done about it; we've gone round on the issue a couple
of times (see the pghackers archives).  My own thought is that the
query as given above should be illegal, since there is no unique
value of datetime to go with a "distinct" set of oid, title, image.

You might consider using GROUP BY rather than DISTINCT if you want
to order the results in a particular way, say
	SELECT oid, title, image ...
		GROUP BY oid, title, image ORDER BY min(datetime)
where you use an aggregate like min or max to resolve the ambiguity
about which datetime to associate with a particular group...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Slashdot Query
@ 1999-06-04 16:10  Chris Bitmead <chris.bitmead@bigfoot.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 7+ messages in thread

From: Chris Bitmead @ 1999-06-04 16:10 UTC (permalink / raw)
  To: pgsql-sql@hub.org

Tom Lane wrote:

> It is not real clear what the correct behavior is, which is why
> nothing's been done about it; we've gone round on the issue a couple
> of times (see the pghackers archives).  My own thought is that the
> query as given above should be illegal, since there is no unique
> value of datetime to go with a "distinct" set of oid, title, image.

Are yes I remember the issues now. I guess it would be nice if there was
some clause to specify which to elminate. Like SELECT DISTINCT
ELIMINATING LAST which then uses the ORDER BY clause to decide whether
to eliminate the first or last. But I can see that would be fiddly to
implement.

> 
> You might consider using GROUP BY rather than DISTINCT if you want
> to order the results in a particular way, say
>         SELECT oid, title, image ...
>                 GROUP BY oid, title, image ORDER BY min(datetime)

I'll give it a go.

> where you use an aggregate like min or max to resolve the ambiguity
> about which datetime to associate with a particular group...
> 
>                         regards, tom lane

-- 
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Slashdot Query
@ 1999-06-06 10:59  Chris Bitmead <chris.bitmead@bigfoot.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 7+ messages in thread

From: Chris Bitmead @ 1999-06-06 10:59 UTC (permalink / raw)
  To: pgsql-sql@hub.org

Tom Lane wrote:
> You might consider using GROUP BY rather than DISTINCT if you want
> to order the results in a particular way, say
>         SELECT oid, title, image ...
>                 GROUP BY oid, title, image ORDER BY min(datetime)

I tried the following query and it crashed the backend. (CVS of a couple
of days ago).

SELECT category.oid, category.title, category.image FROM story,
category* WHERE story.category = category.oid AND story.approved GROUP
BY category.oid, category.title, category.image ORDER BY min(datetime);
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible.  Terminating.


-- 
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Slashdot Query
@ 1999-06-06 15:16  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Chris Bitmead <chris.bitmead@bigfoot.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 1999-06-06 15:16 UTC (permalink / raw)
  To: Chris Bitmead <chris.bitmead@bigfoot.com>; +Cc: pgsql-sql@hub.org

Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> SELECT category.oid, category.title, category.image FROM story,
> category* WHERE story.category = category.oid AND story.approved GROUP
> BY category.oid, category.title, category.image ORDER BY min(datetime);
> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated abnormally
>         before or while processing the request.
> We have lost the connection to the backend, so further processing is
> impossible.  Terminating.

This is a known problem --- see my pghackers email of a few days ago,
"inherited GROUP BY is busted".

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: [SQL] Slashdot Query
@ 1999-06-07 21:29  Jackson, DeJuan <djackson@cpsgroup.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Jackson, DeJuan @ 1999-06-07 21:29 UTC (permalink / raw)
  To: Chris Bitmead <chris.bitmead@bigfoot.com>; +Cc: pgsql-sql@hub.org

	Is it me or does this query actually make any sense???
	Is story.datetime a column in your table?

	You might be able to rewrite this query.  I don't know if the
ordering you were trying for is needed, but

		SELECT oid, title, image FROM category* 
		WHERE EXISTS(SELECT 1 FROM story 
	                WHERE story.approved AND story.category =
category.oid);

	should give you the same information.

	Hope this helps,
		-DEJ


> Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> > SELECT category.oid, category.title, category.image FROM story,
> > category* WHERE story.category = category.oid AND story.approved GROUP
> > BY category.oid, category.title, category.image ORDER BY min(datetime);
> > pqReadData() -- backend closed the channel unexpectedly.
> >         This probably means the backend terminated abnormally
> >         before or while processing the request.
> > We have lost the connection to the backend, so further processing is
> > impossible.  Terminating.
> 
> This is a known problem --- see my pghackers email of a few days ago,
> "inherited GROUP BY is busted".
> 
> 			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Slashdot Query
@ 1999-06-08 07:12  Chris Bitmead <chris.bitmead@bigfoot.com>
  parent: Jackson, DeJuan <djackson@cpsgroup.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Chris Bitmead @ 1999-06-08 07:12 UTC (permalink / raw)
  To: pgsql-sql@hub.org

It looks to me like that solution would simply be a list of all the
categories (assuming there is a story in every category). What I want is
the 5 unique categories applying to the most recent stories.

"Jackson, DeJuan" wrote:
> 
>         Is it me or does this query actually make any sense???
>         Is story.datetime a column in your table?

yes

> 
>         You might be able to rewrite this query.  I don't know if the
> ordering you were trying for is needed, but
> 
>                 SELECT oid, title, image FROM category*
>                 WHERE EXISTS(SELECT 1 FROM story
>                         WHERE story.approved AND story.category =
> category.oid);
> 
>         should give you the same information.
> 
>         Hope this helps,
>                 -DEJ
> 
> > Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> > > SELECT category.oid, category.title, category.image FROM story,
> > > category* WHERE story.category = category.oid AND story.approved GROUP
> > > BY category.oid, category.title, category.image ORDER BY min(datetime);
> > > pqReadData() -- backend closed the channel unexpectedly.
> > >         This probably means the backend terminated abnormally
> > >         before or while processing the request.
> > > We have lost the connection to the backend, so further processing is
> > > impossible.  Terminating.
> >
> > This is a known problem --- see my pghackers email of a few days ago,
> > "inherited GROUP BY is busted".
> >
> >                       regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~1999-06-08 07:12 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-04 03:18 Slashdot Query Chris Bitmead <chris.bitmead@bigfoot.com>
1999-06-04 14:21 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-06-04 16:10   ` Chris Bitmead <chris.bitmead@bigfoot.com>
1999-06-06 10:59   ` Chris Bitmead <chris.bitmead@bigfoot.com>
1999-06-06 15:16     ` Tom Lane <tgl@sss.pgh.pa.us>
1999-06-07 21:29 RE: [SQL] Slashdot Query Jackson, DeJuan <djackson@cpsgroup.com>
1999-06-08 07:12 ` Chris Bitmead <chris.bitmead@bigfoot.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox