pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Roberto Mello <rmello@cc.usu.edu>
To: Steve Meynell <steve@candata.com>
Cc: Postgresql Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Help Retrieving Latest Record
Date: Fri, 16 Feb 2001 13:39:58 -0700
Message-ID: <20010216133958.A15334@cc.usu.edu> (raw)
In-Reply-To: <3A8D3C04.3D81B5BA@candata.com>
References: <3A8D3C04.3D81B5BA@candata.com>

On Fri, Feb 16, 2001 at 09:41:09AM -0500, Steve Meynell wrote:
> 
> I know this doesn't work but I need something like it.
> or something like
> 
> select * from basket where max(date) and fruit='Apples';
> This would yield me:
> 15                                Apples          July 20, 1999

	Maybe you mean min? This is the earliest date you had in the set.

	Assuming the Date column is of datetime/timestamp type:

test=# \d fruits
                Table "fruits"
 Attribute |         Type          | Modifier
-----------+-----------------------+----------
 fruit_id  | integer               |
 name      | character varying(50) |
 date      | timestamp             |

test=# select * from fruits;
 fruit_id |   name    |          date
----------+-----------+------------------------
        1 | Orange    | 2001-02-16 13:26:52-07
        3 | Pineapple | 1999-10-04 00:00:00-06
        2 | Apple     | 2000-12-05 00:00:00-07
        4 | Apple     | 2000-07-01 00:00:00-06
(4 rows)

test=#  SELECT * FROM fruits WHERE name='Apple' ORDER BY date DESC LIMIT
1;
 fruit_id | name  |          date
----------+-------+------------------------
        2 | Apple | 2000-12-05 00:00:00-07
(1 row)

	Look at the documentation for the ORDER BY and LIMIT clauses on the PG
docs.

	-Roberto
-- 
Computer Science			Utah State University
Space Dynamics Laboratory		Web Developer
USU Free Software & GNU/Linux Club 	http://fslc.usu.edu
My home page - http://www.brasileiro.net/roberto



view thread (6+ messages)  latest in thread

Message-ID: <20010216133958.A15334@cc.usu.edu>
Permalink:  ../20010216133958.A15334@cc.usu.edu/
Also on:    postgresql.org/message-id/20010216133958.A15334@cc.usu.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: rmello@cc.usu.edu, steve@candata.com
  Subject: Re: Help Retrieving Latest Record
  In-Reply-To: <20010216133958.A15334@cc.usu.edu>
  References: <3A8D3C04.3D81B5BA@candata.com> <20010216133958.A15334@cc.usu.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox