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.
Sent:Friday, June 02, 2006 10:45 To:Collin Peters [EMAIL PROTECTED]; Cc: pgsql-sql@postgresql.org; Subject:Re: [SQL] Am I crazy or is this SQL not possible
Collin Peters wrote:> I am having some serious mental block here. Here is the abstract> version of my problem. I have a table lik
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
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 2005-04-01 30
3
20 matches
Mail list logo
