Path: news.easynews.com!easynews!feed2.onemain.com!feed1.onemain.com!newsfeeds.belnet.be!news.belnet.be!cleanfeed.casema.net!leda.casema.net!castor.casema.net.POSTED!not-for-mail From: "Radu Lascae" Newsgroups: comp.databases,comp.databases.theory,comp.databases.oracle.misc References: <47c6b9be.0110150215.311d928d@posting.google.com> Subject: Re: Latest visited products query Lines: 54 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2919.6600 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 Message-ID: Date: Mon, 15 Oct 2001 18:38:47 GMT NNTP-Posting-Host: 213.17.3.1 X-Trace: castor.casema.net 1003171127 213.17.3.1 (Mon, 15 Oct 2001 20:38:47 MEST) NNTP-Posting-Date: Mon, 15 Oct 2001 20:38:47 MEST Organization: EuroNet Internet Xref: easynews comp.databases:90117 comp.databases.theory:18721 comp.databases.oracle.misc:70370 X-Received-Date: Mon, 15 Oct 2001 12:11:50 MST (news.easynews.com) The following should work with most database products. select productid, max(mydate) lastdate from product_visit group by product_visit order by lastdate desc; Some databases will not accept using the alias in the order by clause, use then: order by max(mydate); HTH, Radu "Jacob Nordgren" wrote in message news:47c6b9be.0110150215.311d928d@posting.google.com... > 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