SQL Count and Sort? [message #446816] |
Wed, 10 March 2010 10:41  |
johnalex13
Messages: 1 Registered: March 2010
|
Junior Member |
|
|
All,
I am running this querry but am not getting data that is correct. I can not find out where I am going wrong. Anyone see any syntax issues with this?
SELECT a.prod_id, a.prod_name, a.artist_name, COUNT(*)
FROM po_my_purchase_tb a, cm_track_tb b
WHERE a.prod_id = b.prod_id and b.GNR_CD = 'GR000017' AND a.purchase_date > '10-FEB-10' AND ROWNUM<50
GROUP BY a.prod_id, a.prod_name, a.artist_name, a.buy_seq
ORDER BY COUNT(*) desc
|
|
|
Re: SQL Count and Sort? [message #446817 is a reply to message #446816] |
Wed, 10 March 2010 10:54   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Probably you want this:
SELECT * FROM (
SELECT a.prod_id, a.prod_name, a.artist_name, COUNT(*)
FROM po_my_purchase_tb a, cm_track_tb b
WHERE a.prod_id = b.prod_id and b.GNR_CD = 'GR000017' AND a.purchase_date > '10-FEB-10'
GROUP BY a.prod_id, a.prod_name, a.artist_name, a.buy_seq
ORDER BY COUNT(*) desc)
WHERE ROWNUM<50;
The order by is done after the where clause, including the rownum restriction. But usually you want the rownum restriction to kick in after ordering, so you need to nest the query.
Also this:
a.purchase_date > '10-FEB-10'
Should be:
a.purchase_date > to_date('10-02-2010', 'DD-MM-YYYY')
Never rely on implicit conversion for dates and always use 4 digit years - 2 digit years was what caused the y2k bug.
EDIT: forgot the format mask - doh!
2nd EDIT: fixed format masks properly
[Updated on: Wed, 10 March 2010 11:51] Report message to a moderator
|
|
|
|
|
|
|
|
|
|