Re: Latest visited products query

From: Marc Blum <marc_at_marcblum.de>
Date: Mon, 15 Oct 2001 10:25:22 GMT
Message-ID: <3bcab824.1662210@news.online.de>

On 15 Oct 2001 03:15:30 -0700, jacob_nordgren_at_hotmail.com (Jacob Nordgren) wrote:

>Hi,
>
>How can I get a list by the latest viewed products.
>
>This is my table:
>
>CREATE TABLE PRODUCT_VISIT (
> PRODUCTID NUMBER (38) NOT NULL,
> USERID NUMBER (38) NOT NULL,
> MYDATE DATE NOT NULL,
> CONSTRAINT PRODUCT_VISIT_PK
> PRIMARY KEY ( PRODUCTID, USERID, MYDATE ) ) ;
>
>ALTER TABLE PRODUCT_VISIT ADD CONSTRAINT PRODUCTPRODUCTVISIT FOREIGN
>KEY (PRODUCTID)
> REFERENCES STORES (ID) ;
>
>ALTER TABLE STORE_MRAGENT_VISIT ADD CONSTRAINT USERPRODUCTVISIT
> FOREIGN KEY (USERID)
> REFERENCES USERS (ID) ;
>
>
>This is my query:
>
>SELECT DISTINCT PV.PRODUCTID FROM PRODUCT_VISIT PV ORDER BY PV.MYDATE
>
>However, the query isn't working. What I want is a list of product ids
>ordered by the date visited (last visited should be first). One
>product should only appear one time.
>
>What am I doing wrong?
>
>/ Jacob

If you're using 8.1.6 Enterprise Edition or higher, the new analytic function will do the trick:

SELECT productid,

                   mydate

FROM (
SELECT pv.productid,

                  mydate,
                  RANK() OVER (PARTITION BY productid ORDER BY mydate

DESC) as rk
FROM product_visit pv
) v
WHERE rk = 1
ORDER BY mydate DESC

Did it out of my head, hope there are no syntax errors.

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Mon Oct 15 2001 - 05:25:22 CDT