Thread: problem with sql

Hello to everyone,

In my own database, I linked customers table with orders table using 
one-to-many relation. I need to check status of last order for each 
customer and then set customer's status. I made a query using LAST and 
GROUP BY to select last order for each customer and I wanted to use it 
in UPDATE query, but it seems to be impossible. Is here anybody who 
knows how to solve this problem? (I work with MS Access).

Adam

Hi Adam,

On Monday, Oct 20, 2003, at 01:56 Asia/Tokyo, Adaś wrote:
> status. I made a query using LAST and GROUP BY to select last order
> for each customer and I wanted to use it in UPDATE query, but it seems
> to be impossible.

I don't believe PostgreSQL has a built-in function similar to MS
Access' LAST. Something like the following might work for you:

SELECT DISTINCT ON (client) client, order_number, order_date FROM
orders ORDER BY order_date desc

You might have to change the DISTINCT ON terms to match what you want.
Check the docs for usage of DISTINCT ON.

Also, I've heard it's quite easy to write a custom function to do what
you want using CREATE FUNCTION. Haven't done it myself.

Hope it helps.

Michael