SQL QUERY PROBLEM : ORDER BY POPULARITY THEN DISTINCT

  • Thread starter Thread starter jonathan.cohen150887
  • Start date Start date
J

jonathan.cohen150887

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
 
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
'====================================================
 
Back
Top