SQL QUERY PROBLEM : ORDER BY POPULARITY THEN DISTINCT

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jonathan.cohen150887@gmail.com - 16 Apr 2008 04:46 GMT

Hi,

I got the following query.

The thing is that I would like to order the results in order of
popularity and then distinct it so i dont have repeating results.
Problem is these two conflict each other so I dunno wot to do.Could
anyone help me correct this?

Thanks

SELECT Book_Stock.BS_TITLE, Genre.G_TYPE
FROM Book_Stock, Genre, Customer_Orders_Books
WHERE Book_Stock.BS_GENRE= Genre.GENRE_Ref
AND Book_Stock.ISBN_Ref = Customer_Orders_Books.ISBN_Ref
AND Genre.GENRE_Ref IN
(SELECT Genre.GENRE_Ref
FROM Book_Stock, Genre
WHERE Genre.GENRE_Ref = "01"
AND Book_Stock.BS_GENRE = Genre.GENRE_Ref)
GROUP BY Genre.G_TYPE, Book_Stock.BS_TITLE,
Customer_Orders_Books.CUSORDER_Ref
ORDER BY COUNT(Book_Stock.BS_TITLE) DESC

Reply to this Message

John Spencer - 17 Apr 2008 04:35 GMT

Perhaps what you want is the following

SELECT Book_Stock.BS_TITLE
, Genre.G_TYPE
FROM (Book_Stock INNER JOIN Genre)
ON Book_Stock.BS_GENRE= Genre.GENRE_Ref
INNER JOIN Customer_Orders_Books
ON Book_Stock.ISBN_Ref = Customer_Orders_Books.ISBN_Ref
WHERE Genre.GENRE_Ref = "01"

GROUP BY Genre.G_TYPE
, Book_Stock.BS_TITLE

ORDER BY COUNT(Book_Stock.BS_TITLE) DESC

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> Hi,
>
[quoted text clipped - 19 lines]
> Customer_Orders_Books.CUSORDER_Ref
> ORDER BY COUNT(Book_Stock.BS_TITLE) DESC

Reply to this Message