pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDISTINCT ON troubles
6+ messages / 4 participants
[nested] [flat]
* DISTINCT ON troubles
@ 2004-02-19 18:10 Jeremy Smith <jer@highboard.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jeremy Smith @ 2004-02-19 18:10 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a query that reads:
SELECT DISTINCT ON (messageboard.threadid) messageboard.threadid,
messageboard.topic, owner.ownerid, owner.username FROM messageboard, owner
WHERE messageboard.ownerid=owner.ownerid AND messageboard.leagueid =
'$leagueid' ORDER BY messageboard.messageid DESC LIMIT $entries_on_page
OFFSET $beginThread"
The purpose of this query is to retrieve unique threadid's in the order of
the most recent posts that have been made to each thread. When I use this
query I get an error that:
"SELECT DISTINCT ON expressions must match initial ORDER BY expressions".
Of course, if I put ORDER BY threadid in the query it would order it in the
order that the thread was created, not in the last post made.
I have tried using GROUP BY threadID, I get a similar order.
Am I just approaching this all wrong and need to create a temporary table
and draw from that, or is there a way to salvage this query?
Thanks so much,
Jeremy
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: DISTINCT ON troubles
@ 2004-02-19 19:28 Josh Berkus <josh@agliodbs.com>
parent: Jeremy Smith <jer@highboard.com>
0 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2004-02-19 19:28 UTC (permalink / raw)
To: jer@highboard.com; pgsql-sql
Jeremy,
> Am I just approaching this all wrong and need to create a temporary table
> and draw from that, or is there a way to salvage this query?
Think about using a subquery instead of the DISTINCT ON approach. I don't
think you can get what you want with DISTINCT ON.
A temporary table is not necessary.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: DISTINCT ON troubles
@ 2004-02-19 19:34 Jeremy Smith <jer@highboard.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jeremy Smith @ 2004-02-19 19:34 UTC (permalink / raw)
To: pgsql-sql
Thanks Josh,
I'll do that, I just wasn't sure if I was missing something obvious.
Jeremy
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Thursday, February 19, 2004 2:29 PM
To: jer@highboard.com; pgsql-sql@postgresql.org
Subject: Re: [SQL] DISTINCT ON troubles
Jeremy,
> Am I just approaching this all wrong and need to create a temporary table
> and draw from that, or is there a way to salvage this query?
Think about using a subquery instead of the DISTINCT ON approach. I don't
think you can get what you want with DISTINCT ON.
A temporary table is not necessary.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: DISTINCT ON troubles
@ 2004-02-19 21:06 Brian Knox <laotse@aol.net>
parent: Jeremy Smith <jer@highboard.com>
0 siblings, 1 reply; 6+ messages in thread
From: Brian Knox @ 2004-02-19 21:06 UTC (permalink / raw)
To: pgsql-sql
( sorry if this is a repeat, my mail server is being wonky today )
I'm looking for a way, within SQL, given a starting date and an ending
date, to get back the number of months between the start and end date.
If I "SELECT end_date - start_date", I get back an interval in days; I
need months.
Thanks for any suggestions,
Brian
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: DISTINCT ON troubles
@ 2004-02-19 21:39 scott.marlowe <scott.marlowe@ihs.com>
parent: Brian Knox <laotse@aol.net>
0 siblings, 1 reply; 6+ messages in thread
From: scott.marlowe @ 2004-02-19 21:39 UTC (permalink / raw)
To: Brian Knox <laotse@aol.net>; +Cc: pgsql-sql
On Thu, 19 Feb 2004, Brian Knox wrote:
> ( sorry if this is a repeat, my mail server is being wonky today )
>
> I'm looking for a way, within SQL, given a starting date and an ending
> date, to get back the number of months between the start and end date.
> If I "SELECT end_date - start_date", I get back an interval in days; I
> need months.
Maybe date_part?
select date_part('month','2004-08-02 12:00:00'::timestamp) -
date_part('month','2004-05-01 12:00:00'::timestamp);
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: DISTINCT ON troubles
@ 2004-02-19 21:51 scott.marlowe <scott.marlowe@ihs.com>
parent: scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 0 replies; 6+ messages in thread
From: scott.marlowe @ 2004-02-19 21:51 UTC (permalink / raw)
To: Brian Knox <laotse@aol.net>; +Cc: pgsql-sql
On Thu, 19 Feb 2004, scott.marlowe wrote:
> On Thu, 19 Feb 2004, Brian Knox wrote:
>
> > ( sorry if this is a repeat, my mail server is being wonky today )
> >
> > I'm looking for a way, within SQL, given a starting date and an ending
> > date, to get back the number of months between the start and end date.
> > If I "SELECT end_date - start_date", I get back an interval in days; I
> > need months.
>
> Maybe date_part?
>
> select date_part('month','2004-08-02 12:00:00'::timestamp) -
> date_part('month','2004-05-01 12:00:00'::timestamp);
Note that I think you need a +1 at the end of that...
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-02-19 21:51 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-19 18:10 DISTINCT ON troubles Jeremy Smith <jer@highboard.com>
2004-02-19 19:28 ` Josh Berkus <josh@agliodbs.com>
2004-02-19 19:34 ` Jeremy Smith <jer@highboard.com>
2004-02-19 21:06 ` Brian Knox <laotse@aol.net>
2004-02-19 21:39 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-19 21:51 ` scott.marlowe <scott.marlowe@ihs.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