Re: Latest visited products query

Thanks!!! / Jacob

marc_at_marcblum.de (Marc Blum) wrote in message news:<3bcb2c02.502482_at_news.online.de>...
> On 15 Oct 2001 07:12:17 -0700, jacob_nordgren_at_hotmail.com (Jacob
> Nordgren) wrote:
>
> >Hi again,
> >
> >Not that I'm not greatful but is there maby another query that isn't
> >so database specific?
> >
> >/ Jacob
> >
>
> oh, sorry, that solution is too complicated for this problem. Try
> that:
>
> SELECT productid,
> MAX(mydate) AS last_visit
> FROM product_visit
> GROUP BY
> productid
> ORDER BY 2 DESC;
>
> ;-)
>
>
> >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
>
> regards
> Marc Blum
> mailto:marc_at_marcblum.de
> http://www.marcblum.de
Received on Tue Oct 16 2001 - 10:50:04 CEST