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
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
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
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
, June 02, 2006 10:45To: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 like this: unique_id (PK
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.
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
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
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
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
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
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
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
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
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
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
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: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
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.I can't
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 of
20 matches
Mail list logo
