Re: analyzing query results

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Lonni J Friedman" <netllama(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: analyzing query results
Date: 2007-08-13 20:42:22
Message-ID: 21644.1187037742@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

"Lonni J Friedman" <netllama(at)gmail(dot)com> writes:
> I tried to call DISTINCT ON, with the following query:

> SELECT DISTINCT ON(date_created, cudacode) id,current_status FROM
> cudaapps WHERE (SELECT now() - interval '24 hours' <
> date_created::timestamp)='t' ORDER BY date_created;

No, you're not grokking how to use DISTINCT ON. The DISTINCT part
specifies what rows you want to group together --- here, all the ones
with the same cudacode --- and then ORDER BY has to list all those same
columns *followed by* the ordering condition that determines which row
you want out of the group. What I think you want is

SELECT DISTINCT ON (cudacode) id,current_status
FROM cudaapps
WHERE (SELECT now() - interval '24 hours' < date_created::timestamp)='t'
ORDER BY cudacode, date_created DESC;

which gives you the latest current_status for each cudacode, and then
you filter the uninteresting rows in an outer select:

SELECT id
FROM
(SELECT DISTINCT ON (cudacode) id,current_status
FROM cudaapps
WHERE (SELECT now() - interval '24 hours' < date_created::timestamp)='t'
ORDER BY cudacode, date_created DESC) ss
WHERE current_status = 'FAILED';

>> BTW, why are you writing the timestamp filter condition in such a
>> bizarre way?

> I'm doing this because date_created is of type char(20) rather than a
> normal/expected date datatype. I know, its dumb.

Well, so you have to have the cast, but I'd still think that
WHERE (now() - interval '24 hours') < date_created::timestamptz
would be the best way to express it. The sub-SELECT and the comparison
to 't' are just obscurantism.

Another issue here is whether the date format was chosen to ensure that
textual sorting of the values would give the same result as datewise
sorting. You might get some fairly bizarre misbehavior if the data is
sloppy about spaces instead of zero-fill, for instance. Sometimes it's
worth biting the bullet and fixing the column type ...

regards, tom lane

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Lonni J Friedman 2007-08-13 21:51:03 Re: analyzing query results
Previous Message Howard Eglowstein 2007-08-13 20:41:59 More of a SQL question, I guess.