Home » SQL & PL/SQL » SQL & PL/SQL » SQL Count and Sort? (Oracle, Oracle SQL Developer)
SQL Count and Sort? [message #446816] Wed, 10 March 2010 10:41 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12320
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

Re: SQL Count and Sort? [message #446818 is a reply to message #446816] Wed, 10 March 2010 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'10-FEB-10' is not a DATE it is a STRING.

Regards
Michel
Re: SQL Count and Sort? [message #446820 is a reply to message #446817] Wed, 10 March 2010 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Should be:
a.purchase_date > to_date('10-FEB-2010')

No, it should be:
a.purchase_date > to_date('10/02/2010','DD/MM/YYYY')
because:
SQL> select to_date('10-FEB-2010') from dual;
select to_date('10-FEB-2010') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


Regards
Michel
Re: SQL Count and Sort? [message #446821 is a reply to message #446816] Wed, 10 March 2010 10:57 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I know, was typing too fast, fixed my post already.
Re: SQL Count and Sort? [message #446825 is a reply to message #446821] Wed, 10 March 2010 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is still not correct:
SQL> select to_date('10-FEB-2010', 'DD-MON-YYY') from dual;
select to_date('10-FEB-2010', 'DD-MON-YYY') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

Regards
Michel
Re: SQL Count and Sort? [message #446830 is a reply to message #446816] Wed, 10 March 2010 11:32 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yeah, ok - should have thought of language.
But if you were running with English settings you would have got:
SQL> select to_date('10-FEB-2010', 'DD-MON-YYY') from dual;
select to_date('10-FEB-2010', 'DD-MON-YYY') from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


Seems I'm not the only one typing too fast. Smile
Re: SQL Count and Sort? [message #446832 is a reply to message #446830] Wed, 10 March 2010 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Seems I'm not the only one typing too fast.

Note that I copied and pasted your post and didn't type the formula.
So it seems you are the only one to type too fast. Smile

Regards
Michel
Re: SQL Count and Sort? [message #446836 is a reply to message #446816] Wed, 10 March 2010 11:53 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
*sigh*
Yep, all me. Need more coffee.
Previous Topic: Error Reading A File
Next Topic: useful analysis from history tables
Goto Forum:
  


Current Time: Tue Sep 27 16:06:45 CDT 2016

Total time taken to generate the page: 0.11054 seconds