From: Brook Milligan <brook@trillium.NMSU.Edu>
To: chairudin@prima.net.id
Cc: jas1@scigen.co.uk
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Finding the "most recent" rows
Date: Thu, 22 Apr 1999 22:10:04 -0600 (MDT)
Message-ID: <199904230410.WAA12675@trillium.nmsu.edu> (raw)
In-Reply-To: <37202F8D.B9479A67@prima.net.id>
References: <371F2ED6.29FD9251@scigen.co.uk>
<199904221851.MAA10263@trillium.nmsu.edu>
<37202F8D.B9479A67@prima.net.id>
Your script doesn't show how to get the "most recent" rows.
True enough, but that's just because of the < 3 condition within the
subselect. Take that out and you'll get all the most recent rows (in
this example that means largest order_no, but the same works with
dates or whatever).
What are you trying to show here?
The first query was just showing the table, the second the action of
the relevant select.
I thought this was what you wanted and that you would recognize the
effect of the < 3 condition. I just happened to have this example
already that illustrated the general idea and expected that you would
get the idea. Sorry if I should have been more complete.
I think you could just select the highest id, which means the latest data input.
In this particular case the id and the order_no column happen to yield
the same results (they are both ordered in the same way). That
generally won't be the case. But, if you want the highest id within
each customer category a simple change to the select below will do it.
In any case, to get the largest (or most recent or whatever) anything
in each category you need to do a condition involving a subselect with
an aggregate. Something like the following will give you the largest
(without additional constraint) order_no within each customer.
select * from invoices r
where order_no = (select max (order_no) from invoices r2 where r.customer = r2.customer)
order by r.customer, r.order_no;
Sorry for the confusion.
Cheers,
Brook
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: brook@trillium.NMSU.Edu, chairudin@prima.net.id, jas1@scigen.co.uk
Subject: Re: [SQL] Finding the "most recent" rows
In-Reply-To: <199904230410.WAA12675@trillium.nmsu.edu>
References: <371F2ED6.29FD9251@scigen.co.uk> <199904221851.MAA10263@trillium.nmsu.edu> <37202F8D.B9479A67@prima.net.id> <199904230410.WAA12675@trillium.nmsu.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