Re: Latest visited products query

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" <jacob_nordgren_at_hotmail.com> wrote in message news:47c6b9be.0110150215.311d928d_at_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
Received on Mon Oct 15 2001 - 20:38:47 CEST