Re: Latest visited products query

Hi again,

Not that I'm not greatful but is there maby another query that isn't so database specific?

/ Jacob

marc_at_marcblum.de (Marc Blum) wrote in message news:<3bcab824.1662210_at_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 - 16:12:17 CEST