pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAm I crazy or is this SQL not possible
20+ messages / 13 participants
[nested] [flat]
* Am I crazy or is this SQL not possible
@ 2006-06-01 17:43 Collin Peters <cadiolis@gmail.com>
0 siblings, 6 replies; 20+ messages in thread
From: Collin Peters @ 2006-06-01 17:43 UTC (permalink / raw)
To: pgsql-sql
I am having some serious mental block here. Here is the abstract
version of my problem. I have a table like this:
unique_id (PK) broadcast_id date_sent status
1 1 2005-04-04 30
2 1 2005-04-01 30
3 1 2005-05-20 10
4 2 2005-05-29 30
So it is a table that stores broadcasts including the broadcast_id,
the date sent, and the status of the broadcast.
What I would like to do is simply get the last date_sent and it's
status for every broadcast. I can't do a GROUP BY because I can't put
an aggregate on the status column.
SELECT MAX(date_sent), status
FROM broadcast_history
GROUP BY broadcast_id
How do I get the status for the most recent date_sent using GROUP BY?
DISTINCT also doesn't work
SELECT DISTINCT ON (email_broadcast_id) *
FROM email_broadcast_history
ORDER BY date_sent
As you have to have the DISTINCT fields matching the ORDER BY fields.
I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent
I keep thinking am I missing something. Does anybody have any ideas?
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 17:59 Oisin Glynn <me@oisinglynn.com>
parent: Collin Peters <cadiolis@gmail.com>
5 siblings, 0 replies; 20+ messages in thread
From: Oisin Glynn @ 2006-06-01 17:59 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; +Cc: pgsql-sql
Collin Peters wrote:
> I am having some serious mental block here. Here is the abstract
> version of my problem. I have a table like this:
>
> unique_id (PK) broadcast_id date_sent status
> 1 1 2005-04-04 30
> 2 1 2005-04-01 30
> 3 1 2005-05-20 10
> 4 2 2005-05-29 30
>
> So it is a table that stores broadcasts including the broadcast_id,
> the date sent, and the status of the broadcast.
>
> What I would like to do is simply get the last date_sent and it's
> status for every broadcast. I can't do a GROUP BY because I can't put
> an aggregate on the status column.
>
> SELECT MAX(date_sent), status
> FROM broadcast_history
> GROUP BY broadcast_id
>
> How do I get the status for the most recent date_sent using GROUP BY?
>
> DISTINCT also doesn't work
>
> SELECT DISTINCT ON (email_broadcast_id) *
> FROM email_broadcast_history
> ORDER BY date_sent
>
> As you have to have the DISTINCT fields matching the ORDER BY fields.
> I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent
>
> I keep thinking am I missing something. Does anybody have any ideas?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
May not be the most efficient but seems to work here.
Select broadcast_id,status from broadcast_history bh1 where
bh1.date_sent = (select max(date_sent) from broadcast_history bh2 where
bh1.broadcast_id=bh2.broadcast_id) order by bh1.broadcast_id;
Oisin
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 18:05 Aaron Bono <postgresql@aranya.com>
parent: Collin Peters <cadiolis@gmail.com>
5 siblings, 0 replies; 20+ messages in thread
From: Aaron Bono @ 2006-06-01 18:05 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; +Cc: pgsql-sql
select my_sub.max_date, broadcast_history.status
from (
SELECT MAX(date_sent) max_date, broadcast_id
FROM broadcast_history
GROUP BY broadcast_id
) my_sub
inner join broadcast_history on (
broadcast_history.broadcast_id = my_sub.broadcast_id
and broadcast_history.date_sent = my_sub.max_date
);
This should work if the combined broadcast_id, date_sent is unique. If not,
you will need to decide what record to pick in case of a tie.
On 6/1/06, Collin Peters <cadiolis@gmail.com> wrote:
>
> I am having some serious mental block here. Here is the abstract
> version of my problem. I have a table like this:
>
> unique_id (PK) broadcast_id date_sent status
> 1 1 2005-04-04 30
> 2 1 2005-04-01 30
> 3 1 2005-05-20 10
> 4 2 2005-05-29 30
>
> So it is a table that stores broadcasts including the broadcast_id,
> the date sent, and the status of the broadcast.
>
> What I would like to do is simply get the last date_sent and it's
> status for every broadcast. I can't do a GROUP BY because I can't put
> an aggregate on the status column.
>
> SELECT MAX(date_sent), status
> FROM broadcast_history
> GROUP BY broadcast_id
>
> How do I get the status for the most recent date_sent using GROUP BY?
>
> DISTINCT also doesn't work
>
> SELECT DISTINCT ON (email_broadcast_id) *
> FROM email_broadcast_history
> ORDER BY date_sent
>
> As you have to have the DISTINCT fields matching the ORDER BY fields.
> I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent
>
> I keep thinking am I missing something. Does anybody have any ideas?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
==================================================================
Aaron Bono
President Aranya Software Technologies, Inc.
http://www.aranya.com We take care of your technology needs.
Phone: (816) 695-6071
==================================================================
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 18:13 Yasir Malik <ymalik@cs.stevens.edu>
parent: Collin Peters <cadiolis@gmail.com>
5 siblings, 4 replies; 20+ messages in thread
From: Yasir Malik @ 2006-06-01 18:13 UTC (permalink / raw)
To: pgsql-sql
> What I would like to do is simply get the last date_sent and it's
> status for every broadcast. I can't do a GROUP BY because I can't put
> an aggregate on the status column.
>
> SELECT MAX(date_sent), status
> FROM broadcast_history
> GROUP BY broadcast_id
>
You could try the following:
select status
from broadcast_history bh
where bh.date_sent =
(select max(bh2.date_sent)
from broadcast_history bh2);
This reminds me of an interview question: I was asked how to get a
maximum column from a table without using max. How would you do that?
Thanks,
Yasir
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 18:29 maTKO <matko.andjelinic@gmail.com>
parent: Collin Peters <cadiolis@gmail.com>
5 siblings, 0 replies; 20+ messages in thread
From: maTKO @ 2006-06-01 18:29 UTC (permalink / raw)
To: pgsql-sql
"Collin Peters" wrote:
> DISTINCT also doesn't work
>
> SELECT DISTINCT ON (email_broadcast_id) *
> FROM email_broadcast_history
> ORDER BY date_sent
>
> As you have to have the DISTINCT fields matching the ORDER BY fields.
> I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent
DISTINCT does work. You don't have to do DISTINCT on date_sent. The
left part of the ORDER BY must match the DISTINCT expression(s):
SELECT DISTINCT ON(broadcast_id) broadcast_id, date_sent, status FROM
table ORDER BY broadcast_id, date_sent DESC;
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 18:38 Aaron Bono <aaron.bono@aranya.com>
parent: Yasir Malik <ymalik@cs.stevens.edu>
3 siblings, 0 replies; 20+ messages in thread
From: Aaron Bono @ 2006-06-01 18:38 UTC (permalink / raw)
To: Yasir Malik <ymalik@cs.stevens.edu>; +Cc: pgsql-sql
> This reminds me of an interview question: I was asked how to get a
> maximum column from a table without using max. How would you do that?
>
>
Select my_column
from my_table
order by my_column desc
limit 1
--
==================================================================
Aaron Bono
President Aranya Software Technologies, Inc.
http://www.aranya.com We take care of your technology needs.
Phone: (816) 695-6071
==================================================================
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 18:39 Aaron Bono <aaron.bono@aranya.com>
parent: Yasir Malik <ymalik@cs.stevens.edu>
3 siblings, 0 replies; 20+ messages in thread
From: Aaron Bono @ 2006-06-01 18:39 UTC (permalink / raw)
To: Yasir Malik <ymalik@cs.stevens.edu>; +Cc: pgsql-sql
I think this approach will only work if each broadcast_id has the same
maximum date_sent value. You really need to do the group by in a
sub-query to bring the broadcast_id together with the max date_sent.
On 6/1/06, Yasir Malik <ymalik@cs.stevens.edu> wrote:
> > What I would like to do is simply get the last date_sent and it's
> > status for every broadcast. I can't do a GROUP BY because I can't put
> > an aggregate on the status column.
> >
> > SELECT MAX(date_sent), status
> > FROM broadcast_history
> > GROUP BY broadcast_id
> >
> You could try the following:
> select status
> from broadcast_history bh
> where bh.date_sent =
> (select max(bh2.date_sent)
> from broadcast_history bh2);
>
==================================================================
Aaron Bono
President Aranya Software Technologies, Inc.
http://www.aranya.com We take care of your technology needs.
Phone: (816) 695-6071
==================================================================
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 18:53 Yasir Malik <ymalik@cs.stevens.edu>
parent: Yasir Malik <ymalik@cs.stevens.edu>
3 siblings, 1 reply; 20+ messages in thread
From: Yasir Malik @ 2006-06-01 18:53 UTC (permalink / raw)
To: pgsql-sql
> select max(date_sent) from table;
> would equal
> select date_sent from broadcast_history order by date_sent DESC limit 1;
>
That sounds like a hack. Is limit a SQL-99 standard? Is there are
another way to do this?
Sorry to take over your topic, Collin.
Thanks,
Yasir
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 19:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Collin Peters <cadiolis@gmail.com>
5 siblings, 1 reply; 20+ messages in thread
From: Tom Lane @ 2006-06-01 19:21 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; +Cc: pgsql-sql
"Collin Peters" <cadiolis@gmail.com> writes:
> What I would like to do is simply get the last date_sent and it's
> status for every broadcast. I can't do a GROUP BY because I can't put
> an aggregate on the status column.
You missed the key idea about how to use DISTINCT ON.
SELECT DISTINCT ON (email_broadcast_id) *
FROM email_broadcast_history
ORDER BY email_broadcast_id, date_sent DESC
You order by the DISTINCT ON fields, then one or more additional fields
to select the representative row you want within each DISTINCT ON group.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 19:30 Aaron Bono <postgresql@aranya.com>
parent: Yasir Malik <ymalik@cs.stevens.edu>
0 siblings, 1 reply; 20+ messages in thread
From: Aaron Bono @ 2006-06-01 19:30 UTC (permalink / raw)
To: Yasir Malik <ymalik@cs.stevens.edu>; +Cc: pgsql-sql
It is a hack, but when someone wants you to do something in a way
different from the norm, aren't they asking for a hack?
SQL Server does something like
select top (1) from ....
I am thinking this is NOT a SQL-99 standard.
-Aaron
On 6/1/06, Yasir Malik <ymalik@cs.stevens.edu> wrote:
> > select max(date_sent) from table;
> > would equal
> > select date_sent from broadcast_history order by date_sent DESC limit 1;
> >
> That sounds like a hack. Is limit a SQL-99 standard? Is there are
> another way to do this?
> Sorry to take over your topic, Collin.
>
> Thanks,
> Yasir
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 19:47 Yasir Malik <ymalik@cs.stevens.edu>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 2 replies; 20+ messages in thread
From: Yasir Malik @ 2006-06-01 19:47 UTC (permalink / raw)
To: pgsql-sql
> It is a hack, but when someone wants you to do something in a way
> different from the norm, aren't they asking for a hack?
>
> SQL Server does something like
> select top (1) from ....
>
> I am thinking this is NOT a SQL-99 standard.
>
This was an interview with Amazon, and I don't think Amazon wanted a hack.
I hope Amazon doesn't use hacks. There has to be another way. It would
be cruel if they expected me know some database specific functionality.
Here's what I gave them:
select value
from table t
where t.value >
(select t2.value
from table t2);
which would be fine if the sub-select returned simply a list, but that
isn't even valid SQL (I didn't get the job, BTW).
Yasir
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 19:54 Alvaro Herrera <alvherre@commandprompt.com>
parent: Yasir Malik <ymalik@cs.stevens.edu>
1 sibling, 0 replies; 20+ messages in thread
From: Alvaro Herrera @ 2006-06-01 19:54 UTC (permalink / raw)
To: Yasir Malik <ymalik@cs.stevens.edu>; +Cc: pgsql-sql
Yasir Malik wrote:
> >It is a hack, but when someone wants you to do something in a way
> >different from the norm, aren't they asking for a hack?
> >
> >SQL Server does something like
> >select top (1) from ....
> >
> >I am thinking this is NOT a SQL-99 standard.
> >
> This was an interview with Amazon, and I don't think Amazon wanted a hack.
> I hope Amazon doesn't use hacks. There has to be another way. It would
> be cruel if they expected me know some database specific functionality.
Do they use PostgreSQL at Amazon? Maybe they wanted to know if you are
aware of common non-standard tricks for whatever RDBMS they use.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 19:54 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Yasir Malik <ymalik@cs.stevens.edu>
1 sibling, 0 replies; 20+ messages in thread
From: Scott Marlowe @ 2006-06-01 19:54 UTC (permalink / raw)
To: Yasir Malik <ymalik@cs.stevens.edu>; +Cc: pgsql-sql
On Thu, 2006-06-01 at 14:47, Yasir Malik wrote:
> > It is a hack, but when someone wants you to do something in a way
> > different from the norm, aren't they asking for a hack?
> >
> > SQL Server does something like
> > select top (1) from ....
> >
> > I am thinking this is NOT a SQL-99 standard.
> >
> This was an interview with Amazon, and I don't think Amazon wanted a hack.
> I hope Amazon doesn't use hacks. There has to be another way. It would
> be cruel if they expected me know some database specific functionality.
> Here's what I gave them:
> select value
> from table t
> where t.value >
> (select t2.value
> from table t2);
>
> which would be fine if the sub-select returned simply a list, but that
> isn't even valid SQL (I didn't get the job, BTW).
Sometimes the only reasonable answer is a hack.
Much like in the older versions of PostgreSQL where select max(id) from
sometable was slower than watching grass grow, the standard "hack" was
to do select id from sometable order by id desc limit 1.
If the SQL way kills your server and the hack doesn't, you use the hack
and move on.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 20:06 mark.dingee@cox.net
0 siblings, 0 replies; 20+ messages in thread
From: mark.dingee@cox.net @ 2006-06-01 20:06 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; +Cc: pgsql-sql
Collin,
I have a similar circumstance in one of my own apps. I operate under
the simple presumption that the unique_id is sequential and thus the
record with the highest unique_id is the most recent entry. In that
case I use a query such as
select *
from broadcast_history
where unique_id in (
select broadcast_id, max(unique_id)
from broadcast_history
group by broadcast_id)
which permits me to examine the entire record which is necessary in my
situation.
Good luck
Mark
On Thu, 2006-06-01 at 10:43 -0700, Collin Peters wrote:
> I am having some serious mental block here. Here is the abstract
> version of my problem. I have a table like this:
>
> unique_id (PK) broadcast_id date_sent status
> 1 1 2005-04-04 30
> 2 1 2005-04-01 30
> 3 1 2005-05-20 10
> 4 2 2005-05-29 30
>
> So it is a table that stores broadcasts including the broadcast_id,
> the date sent, and the status of the broadcast.
>
> What I would like to do is simply get the last date_sent and it's
> status for every broadcast. I can't do a GROUP BY because I can't put
> an aggregate on the status column.
>
> SELECT MAX(date_sent), status
> FROM broadcast_history
> GROUP BY broadcast_id
>
> How do I get the status for the most recent date_sent using GROUP BY?
>
> DISTINCT also doesn't work
>
> SELECT DISTINCT ON (email_broadcast_id) *
> FROM email_broadcast_history
> ORDER BY date_sent
>
> As you have to have the DISTINCT fields matching the ORDER BY fields.
> I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent
>
> I keep thinking am I missing something. Does anybody have any ideas?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-01 20:26 Rod Taylor <pg@rbt.ca>
parent: Yasir Malik <ymalik@cs.stevens.edu>
3 siblings, 0 replies; 20+ messages in thread
From: Rod Taylor @ 2006-06-01 20:26 UTC (permalink / raw)
To: Yasir Malik <ymalik@cs.stevens.edu>; +Cc: pgsql-sql
On Thu, 2006-06-01 at 14:13 -0400, Yasir Malik wrote:
> > What I would like to do is simply get the last date_sent and it's
> > status for every broadcast. I can't do a GROUP BY because I can't put
> > an aggregate on the status column.
> >
> > SELECT MAX(date_sent), status
> > FROM broadcast_history
> > GROUP BY broadcast_id
> >
> You could try the following:
> select status
> from broadcast_history bh
> where bh.date_sent =
> (select max(bh2.date_sent)
> from broadcast_history bh2);
>
> This reminds me of an interview question: I was asked how to get a
> maximum column from a table without using max. How would you do that?
Find the list of everything that isn't the highest value, then invert
it.
Don't expect it to perform very well though.
select col
from foo
where col not in
(select f1.col
from foo as f1
join foo as f2 on (f1.col < f2.col)
);
--
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-02 01:44 Aaron Bono <postgresql@aranya.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 20+ messages in thread
From: Aaron Bono @ 2006-06-02 01:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Is this SQL-99 compliant or a PostgreSQL specific query? I really like it
and have never seen this before.
-Aaron
On 6/1/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Collin Peters" <cadiolis@gmail.com> writes:
> > What I would like to do is simply get the last date_sent and it's
> > status for every broadcast. I can't do a GROUP BY because I can't put
> > an aggregate on the status column.
>
> You missed the key idea about how to use DISTINCT ON.
>
> SELECT DISTINCT ON (email_broadcast_id) *
> FROM email_broadcast_history
> ORDER BY email_broadcast_id, date_sent DESC
>
> You order by the DISTINCT ON fields, then one or more additional fields
> to select the representative row you want within each DISTINCT ON group.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-02 02:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 20+ messages in thread
From: Tom Lane @ 2006-06-02 02:41 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-sql
"Aaron Bono" <postgresql@aranya.com> writes:
> Is this SQL-99 compliant or a PostgreSQL specific query? I really like it
> and have never seen this before.
DISTINCT ON is a Postgres-ism, I'm afraid. It's pretty handy though.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-02 08:45 Patrick Jacquot <patrick.jacquot@anpe.fr>
parent: Collin Peters <cadiolis@gmail.com>
5 siblings, 0 replies; 20+ messages in thread
From: Patrick Jacquot @ 2006-06-02 08:45 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; +Cc: pgsql-sql
Collin Peters wrote:
> I am having some serious mental block here. Here is the abstract
> version of my problem. I have a table like this:
>
> unique_id (PK) broadcast_id date_sent status
> 1 1 2005-04-04 30
> 2 1 2005-04-01 30
> 3 1 2005-05-20 10
> 4 2 2005-05-29 30
>
> So it is a table that stores broadcasts including the broadcast_id,
> the date sent, and the status of the broadcast.
>
> What I would like to do is simply get the last date_sent and it's
> status for every broadcast. I can't do a GROUP BY because I can't put
> an aggregate on the status column.
>
> SELECT MAX(date_sent), status
> FROM broadcast_history
> GROUP BY broadcast_id
>
> How do I get the status for the most recent date_sent using GROUP BY?
>
> DISTINCT also doesn't work
>
> SELECT DISTINCT ON (email_broadcast_id) *
> FROM email_broadcast_history
> ORDER BY date_sent
>
> As you have to have the DISTINCT fields matching the ORDER BY fields.
> I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent
>
> I keep thinking am I missing something. Does anybody have any ideas?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
You can also try
SELECT * from broadcast_history A WHERE NOT EXISTS
(SELECT * from broadcast_history B WHERE B.date_sent >A.date_sent)
There isn't any PostgreSQL-ism, just a correlated subrequest wich is
perfectly standars, afaik
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-02 11:53 Klay Martens <kmartens@wol.co.za>
0 siblings, 1 reply; 20+ messages in thread
From: Klay Martens @ 2006-06-02 11:53 UTC (permalink / raw)
To: patrick.jacquot@anpe.fr; cadiolis@gmail.com; +Cc: pgsql-sql
<BR>Sorry to stick my nose in here...<BR>would not this work better?<BR><BR>SELECT broadcast_id,date_sent,status from broadcast_history where<BR>unique_id in (<BR>SELECT max(unique_id) from broadcast_history group by broadcast_id);<BR><BR>Seems like a simpler option.<BR><BR>
<P> <FONT size=2>----- Original Message ------<BR> <B>From:</B>Patrick Jacquot<BR> <B>Sent:</B>Friday, June 02, 2006 10:45<BR> <B>To:</B>Collin Peters cadiolis@gmail.com; <BR> <B>Cc: </B>pgsql-sql@postgresql.org; <BR> <B>Subject:</B>Re: [SQL] Am I crazy or is this SQL not possible</FONT>
<P>
<TABLE>
<TBODY>
<TR>
<TD>
<META content="text/html; charset=windows-874" http-eqviv="Content-Type"><FONT size=2>Collin Peters wrote:<BR><BR>> I am having some serious mental block here. Here is the abstract<BR>> version of my problem. I have a table like this:<BR>><BR>> unique_id (PK) broadcast_id date_sent status<BR>> 1 1 2005-04-04 30<BR>> 2 1 2005-04-01 30<BR>> 3 1 2005-05-20 10<BR>> 4 2 2005-05-29 30<BR>><BR>> So it is a table that stores broadcasts including the broadcast_id,<BR>> the date sent, and the status of the broadcast.<BR>><BR>> What I would like to do is simply get the last date_sent and it's<BR>> status for every broadcast. I can't do a GROUP BY because I can't put<BR>> an aggregate on the status column.<BR>><BR>> SELECT MAX(date_sent), status<BR>> FROM broadcast_history<BR>> GROUP BY broadcast_id<BR>><BR>> How do I get the status for the most recent date_sent using GROUP BY?<BR>><BR>> DISTINCT also doesn't work<BR>><BR>> SELECT DISTINCT ON (email_broadcast_id) *<BR>> FROM email_broadcast_history<BR>> ORDER BY date_sent<BR>><BR>> As you have to have the DISTINCT fields matching the ORDER BY fields.<BR>> I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent<BR>><BR>> I keep thinking am I missing something. Does anybody have any ideas?<BR>><BR>> ---------------------------(end of broadcast)---------------------------<BR>> TIP 2: Don't 'kill -9' the postmaster<BR>><BR>You can also try<BR>SELECT * from broadcast_history A WHERE NOT EXISTS<BR>(SELECT * from broadcast_history B WHERE B.date_sent >A.date_sent)<BR>There isn't any PostgreSQL-ism, just a correlated subrequest wich is <BR>perfectly standars, afaik<BR><BR><BR> ---------------------------(end of broadcast)---------------------------<BR> TIP 9: In versions below 8.0, the planner will ignore your desire to<BR> choose an index scan if your joining column's datatypes do not<BR> match<BR> <BR></FONT></TD></TR></TBODY></TABLE></P>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Am I crazy or is this SQL not possible
@ 2006-06-02 15:23 Aaron Bono <postgresql@aranya.com>
parent: Klay Martens <kmartens@wol.co.za>
0 siblings, 0 replies; 20+ messages in thread
From: Aaron Bono @ 2006-06-02 15:23 UTC (permalink / raw)
To: Klay Martens <kmartens@wol.co.za>; +Cc: patrick.jacquot@anpe.fr; cadiolis@gmail.com; pgsql-sql
Only if you assume that ordering by unique_id and by date_sent are
equivalent. That may be the case but I personally hate making assumptions
like that. When someone goes into the database and updates records (clean
up bad data, etc.) your perfectly running query can suddenly produce bad
results.
-Aaron
On 6/2/06, Klay Martens <kmartens@wol.co.za> wrote:
>
>
> Sorry to stick my nose in here...
> would not this work better?
>
> SELECT broadcast_id,date_sent,status from broadcast_history where
> unique_id in (
> SELECT max(unique_id) from broadcast_history group by broadcast_id);
>
> Seems like a simpler option.
>
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2006-06-02 15:23 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-01 17:43 Am I crazy or is this SQL not possible Collin Peters <cadiolis@gmail.com>
2006-06-01 17:59 ` Oisin Glynn <me@oisinglynn.com>
2006-06-01 18:05 ` Aaron Bono <postgresql@aranya.com>
2006-06-01 18:13 ` Yasir Malik <ymalik@cs.stevens.edu>
2006-06-01 18:38 ` Aaron Bono <aaron.bono@aranya.com>
2006-06-01 18:39 ` Aaron Bono <aaron.bono@aranya.com>
2006-06-01 18:53 ` Yasir Malik <ymalik@cs.stevens.edu>
2006-06-01 19:30 ` Aaron Bono <postgresql@aranya.com>
2006-06-01 19:47 ` Yasir Malik <ymalik@cs.stevens.edu>
2006-06-01 19:54 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-06-01 19:54 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-06-01 20:26 ` Rod Taylor <pg@rbt.ca>
2006-06-01 18:29 ` maTKO <matko.andjelinic@gmail.com>
2006-06-01 19:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-02 01:44 ` Aaron Bono <postgresql@aranya.com>
2006-06-02 02:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-02 08:45 ` Patrick Jacquot <patrick.jacquot@anpe.fr>
2006-06-01 20:06 Re: Am I crazy or is this SQL not possible mark.dingee@cox.net
2006-06-02 11:53 Re: Am I crazy or is this SQL not possible Klay Martens <kmartens@wol.co.za>
2006-06-02 15:23 ` Aaron Bono <postgresql@aranya.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