Re: Date operations - Mailing list pgsql-novice

On 03/01/2011 08:14 AM, Chetan Suttraway wrote:

What I would like, if possible, is something on the line of "SELECT
DISTINCT ON file ..... WHERE date NOT GREATER THAN somedate [ORDER BY
date desc]" but cannot seem to find a way to do it.

I would greatly appreciate any ideas on this subject, kind regards to all,
Barbara F.

Would it be possible to post the setup in terms of sql queries?

Hello, thanks for your answer.
The view in question has this definition:
--
CREATE VIEW olx_gst_exp AS
    SELECT e.exp_name, e.exp_number, g.gest_fecha, g.gest_tipo FROM olx_expedient e, olx_gestiones g WHERE (e.exp_id = g.olx_exp_id) ORDER BY e.exp_name;

whereas the corresponding tables have this setup:

openlex=# \d olx_expedient
                                             Table "public.olx_expedient"
     Column            |           Type                         |            Modifiers                                
----------------+--------------------------+--------------------------------------------------------------------------
 exp_number        | text                                    | not null
 exp_ext_id           | integer                               |
 crt_id                   | integer                               |
 exp_sec_id           | integer                               |
 exp_start             | timestamp with time zone | default now()
 exp_end              | timestamp with time zone |
 exp_user_stamp | character varying(64)        | default "current_user"()
 exp_time_stamp | timestamp with time zone | default now()
 exp_id                 | integer                               | not null default nextval(('"olx_expedient_exp_id_seq"'::text)::regclass)
 exp_name          | character varying(64)        |
 exp_nick             | text                                    |
activo                 | boolean                              | default true
....

and

openlex=# \d olx_gestiones
                                       Table "public.olx_gestiones"
     Column           |           Type           |                          Modifiers
                         
----------------+--------------------------+--------------------------------------------------------------
 gest_id                | integer                  | default nextval(('"olx_gestiones_gest_id"'::text)::regclass)
 gest_tipo             | text                       |
 gest_fecha          | date                      | default ('now'::text)::date
 olx_exp_id           | integer                  | not null
 plazo                   | interval                 |
 fechas_cl            | date                      |
 vencim               | integer                  |
 timestamp         | timestamp with time zone | default now()
 fojas                   | integer                  |
 exp_user_stamp | text                     | default "current_user"()


I hope this is clear enough, thanks again for your response.
Barbara F.

-- 
Dra. Bárbara M. Figueirido
25 de Mayo 1331
8400 S.C. de Bariloche - RIO NEGRO
Argentina
Tel.-Fax: +54 2944 456252                  15682745