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 <[EMAIL PROTECTED]> wrote:
> What I would like to do is simply get
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
"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
DIS
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.
Sorry to stick my nose in here...would not this work better?SELECT broadcast_id,date_sent,status from broadcast_history whereunique_id in (SELECT max(unique_id) from broadcast_history group by broadcast_id);Seems like a simpler option.
- Original Message -- From:Patrick Jacquot Sent:Friday
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-0430
2 1
"Aaron Bono" <[EMAIL PROTECTED]> 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
---(end
Is this SQL-99 compliant or a PostgreSQL specific query? I really like it and have never seen this before.-AaronOn 6/1/06, Tom Lane <
[EMAIL PROTECTED]> wrote:"Collin Peters" <
[EMAIL PROTECTED]> writes:> What I would like to do is simply get the last date_sent and it's> status for every broadcast
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_hist
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 (
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.
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 A
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 hac
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 <[EMAIL PROTECTED]> wrote:
> select max(
"Collin Peters" <[EMAIL PROTECTED]> 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 (e
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
---(e
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
fro
select my_sub.max_date, broadcast_history.statusfrom (SELECT MAX(date_sent) max_date, broadcast_idFROM broadcast_historyGROUP BY broadcast_id) my_subinner join broadcast_history on (broadcast_history.broadcast_id = my_sub.broadcast_id
and broadcast_history.date_sent = my_sub.max_date);This should w
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-0430
2 1
19 matches
Mail list logo
