Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL Question

SQL Question

From
Alex
Date:
Hi,

i have a table

ProdId | LastUpdate
-------+------------
100    | 2005-04-01
100    | 2005-03-01
100    | 2005-02-01
200    | 2005-04-01
200    | 2005-03-01
200    | 2005-02-01

- How can i select only the newest record for each ProdId ?

100    | 2005-04-01
200    | 2005-04-01

- How can i select to retrieve the last 2 dates in record

100    | 2005-04-01 | 2005-03-01
200    | 2005-04-01 | 2005-03-01


Thanks
Alex

Re: SQL Question

From
Thomas Kellerer
Date:
On 15.04.2005 13:58 Alex wrote:

> Hi,
>
> i have a table
>
> ProdId | LastUpdate
> -------+------------
> 100    | 2005-04-01
> 100    | 2005-03-01
> 100    | 2005-02-01
> 200    | 2005-04-01
> 200    | 2005-03-01
> 200    | 2005-02-01
>
> - How can i select only the newest record for each ProdId ?
>
> 100    | 2005-04-01
> 200    | 2005-04-01
>
SELECT prodid,
       lastupdate
FROM produpdate p1
WHERE lastupdate = (SELECT MAX (lastupdate) FROM produpdate p2 WHERE p2.prodid =
p1.prodid)

Thomas

Re: SQL Question

From
"GIROIRE, Nicolas (COFRAMI)"
Date:
For the first request (How can i select only the newest record for each ProdId ?), you can do :

select * from test."tableProd" u
    where u."LastUpdate" = (select max(t."LastUpdate")
                    from test."tableProd" t
                    where u."ProdId" = t."ProdId")





-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de Alex
Envoy� : vendredi 15 avril 2005 13:59
� : pgsql-general@postgresql.org
Objet : [GENERAL] SQL Question


Hi,

i have a table

ProdId | LastUpdate
-------+------------
100    | 2005-04-01
100    | 2005-03-01
100    | 2005-02-01
200    | 2005-04-01
200    | 2005-03-01
200    | 2005-02-01

- How can i select only the newest record for each ProdId ?

100    | 2005-04-01
200    | 2005-04-01

- How can i select to retrieve the last 2 dates in record

100    | 2005-04-01 | 2005-03-01
200    | 2005-04-01 | 2005-03-01


Thanks
Alex



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.
Security Notice: all e-mail, sent to or from this address, may be
accessed by someone other than the recipient, for system management and
security reasons. This access is controlled under Regulation of
Investigatory Powers Act 2000, Lawful Business Practises.

Re: SQL Question

From
Richard Huxton
Date:
GIROIRE, Nicolas (COFRAMI) wrote:
> For the first request (How can i select only the newest record for each ProdId ?), you can do :
>
> select * from test."tableProd" u
>     where u."LastUpdate" = (select max(t."LastUpdate")
>                     from test."tableProd" t
>                     where u."ProdId" = t."ProdId")

Although this only guarantees one row if LastUpdate is unique for every
ProdId.
--
   Richard Huxton
   Archonet Ltd

Re: SQL Question

From
"GIROIRE, Nicolas (COFRAMI)"
Date:
Ok but you haven't specify that and in your example there is no similar use

one solution is to change LastUpdate type and use timestamp.

to insert you make :
insert into test."tableProd" values (100, '2004-05-01 02:52:12') but it exists other format for time....see postgresql
docfor different type (here the french version http://traduc.postgresqlfr.org/pgsql-fr/datatype-datetime.html) 

With timestamp the accuracy is better, you have until second.




-----Message d'origine-----
De : Richard Huxton [mailto:dev@archonet.com]
Envoy� : vendredi 15 avril 2005 15:42
� : GIROIRE, Nicolas (COFRAMI)
Cc : Alex; pgsql-general@postgresql.org
Objet : Re: [GENERAL] SQL Question


GIROIRE, Nicolas (COFRAMI) wrote:
> For the first request (How can i select only the newest record for each ProdId ?), you can do :
>
> select * from test."tableProd" u
>     where u."LastUpdate" = (select max(t."LastUpdate")
>                     from test."tableProd" t
>                     where u."ProdId" = t."ProdId")

Although this only guarantees one row if LastUpdate is unique for every
ProdId.
--
   Richard Huxton
   Archonet Ltd

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.
Security Notice: all e-mail, sent to or from this address, may be
accessed by someone other than the recipient, for system management and
security reasons. This access is controlled under Regulation of
Investigatory Powers Act 2000, Lawful Business Practises.

Re: SQL Question

From
Date:
select max(lastupdate),prodid
from tablename
group by prodid


-----Original Message-----

From:  Thomas Kellerer <spam_eater@gmx.net>
Subj:  Re: [GENERAL] SQL Question
Date:  Fri Apr 15, 2005 7:24 am
Size:  621 bytes
To:  pgsql-general@postgresql.org

On 15.04.2005 13:58 Alex wrote:

> Hi,
>
> i have a table
>
> ProdId | LastUpdate
> -------+------------
> 100    | 2005-04-01
> 100    | 2005-03-01
> 100    | 2005-02-01
> 200    | 2005-04-01
> 200    | 2005-03-01
> 200    | 2005-02-01
>
> - How can i select only the newest record for each ProdId ?
>
> 100    | 2005-04-01
> 200    | 2005-04-01
>
SELECT prodid,
       lastupdate
FROM produpdate p1
WHERE lastupdate = (SELECT MAX (lastupdate) FROM produpdate p2 WHERE p2.prodid =
p1.prodid)

Thomas


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend




Joseph M Day
Global Innovative Solutions
651 W Washington Blvd
Chicago, IL 60661
D: (312) 371-3054
F: (312) 421-8557

Re: SQL Question

From
Thomas Kellerer
Date:
jday@gisolutions.us wrote on 15.04.2005 16:42:

> select max(lastupdate),prodid
> from tablename
> group by prodid
>

Even better :)

Thomas

Re: SQL Question

From
Bruno Wolff III
Date:
On Fri, Apr 15, 2005 at 21:58:31 +1000,
  Alex <alex@meerkatsoft.com> wrote:
> Hi,
>
> i have a table
>
> ProdId | LastUpdate
> -------+------------
> 100    | 2005-04-01
> 100    | 2005-03-01
> 100    | 2005-02-01
> 200    | 2005-04-01
> 200    | 2005-03-01
> 200    | 2005-02-01
>
> - How can i select only the newest record for each ProdId ?
>
> 100    | 2005-04-01
> 200    | 2005-04-01

You can also use the Postgres specific DISTINCT ON clause.

SELECT DISTINCT ON (prodid) prodid, lastupdate ORDER BY prodid, lastupdate DESC;

Re: SQL Question

From
"Julian Scarfe"
Date:
From: "Alex" <alex@meerkatsoft.com>

> - How can i select only the newest record for each ProdId ?
>
> 100    | 2005-04-01
> 200    | 2005-04-01
>

DISTINCT ON was made for this and on the similar tables I have performs
rather more efficiently than using a subquery.

select distinct on (ProdId) ProdId , LastUpdate
from produpdate
order by ProdId , LastUpdate desc;

> - How can i select to retrieve the last 2 dates in record
>
> 100    | 2005-04-01 | 2005-03-01
> 200    | 2005-04-01 | 2005-03-01

To get the previous one, my first thought is something like:

select distinct on (ProdId) ProdId , LastUpdate
from produpdate p1
where LastUpdate <> (
    select max(LastUpdate ) from produpdate p2 where p2.ProdId = p1.ProdId
)
order by ProdId , LastUpdate desc ;

but there may be a much more efficient way of getting the nth result in
general.

Julian Scarfe

Re: SQL Question

From
Alex
Date:
Julian Scarfe wrote:

> From: "Alex" <alex@meerkatsoft.com>
>
>> - How can i select only the newest record for each ProdId ?
>>
>> 100    | 2005-04-01
>> 200    | 2005-04-01
>>
>
> DISTINCT ON was made for this and on the similar tables I have
> performs rather more efficiently than using a subquery.
>
> select distinct on (ProdId) ProdId , LastUpdate
> from produpdate
> order by ProdId , LastUpdate desc;
>
>> - How can i select to retrieve the last 2 dates in record
>>
>> 100    | 2005-04-01 | 2005-03-01
>> 200    | 2005-04-01 | 2005-03-01
>
>
> To get the previous one, my first thought is something like:
>
> select distinct on (ProdId) ProdId , LastUpdate
> from produpdate p1
> where LastUpdate <> (
>    select max(LastUpdate ) from produpdate p2 where p2.ProdId = p1.ProdId
> )
> order by ProdId , LastUpdate desc ;
>
> but there may be a much more efficient way of getting the nth result
> in general.
>
> Julian Scarfe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>
Thanks for the help. will give it a try.

Alex