Re: Latest visited products query

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

                 productid

>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 Mon Oct 15 2001 - 20:36:22 CEST