Home » SQL & PL/SQL » SQL & PL/SQL » To get top 3 from a table
To get top 3 from a table [message #190536] Thu, 31 August 2006 03:38 Go to next message
trupti.nanajkar
Messages: 4
Registered: August 2006
Junior Member
I have a table error_log which has a column error_code VARCHAR2(10).
It looks like:
ERROR_CODE
VAL_001
VAL_002
VAL_002
VAL_003
VAL_003
VAL_003
REM_001
REM_001
REM_001
REM_001

I need to get the top 3 error codes and their counts.
Please help!!!
Re: To get top 3 from a table [message #190538 is a reply to message #190536] Thu, 31 August 2006 03:46 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Try this:

SQL> CREATE TABLE error_log (error_code VARCHAR2(10));

Table created.

SQL>
SQL> INSERT INTO error_log VALUES ('VAL_001');

1 row created.

SQL> INSERT INTO error_log VALUES ('VAL_002');

1 row created.

SQL> INSERT INTO error_log VALUES ('VAL_002');

1 row created.

SQL> INSERT INTO error_log VALUES ('VAL_003');

1 row created.

SQL> INSERT INTO error_log VALUES ('VAL_003');

1 row created.

SQL> INSERT INTO error_log VALUES ('VAL_003');

1 row created.

SQL> INSERT INTO error_log VALUES ('REM_001');

1 row created.

SQL> INSERT INTO error_log VALUES ('REM_001');

1 row created.

SQL> INSERT INTO error_log VALUES ('REM_001');

1 row created.

SQL> INSERT INTO error_log VALUES ('REM_001');

1 row created.

SQL>
SQL> -- I need to get the top 3 error codes and their counts.
SQL> SELECT * FROM (
  2    SELECT error_code, COUNT(*)
  3      FROM error_log
  4     GROUP BY error_code
  5     ORDER BY COUNT(*) DESC
  6    )
  7  WHERE ROWNUM <= 3;

ERROR_CODE   COUNT(*)
---------- ----------
REM_001             4
VAL_003             3
VAL_002             2
Re: To get top 3 from a table [message #190539 is a reply to message #190536] Thu, 31 August 2006 03:49 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Without using analytics...

SQL> CREATE TABLE ERROR_LOG(ERROR_CODE VARCHAR2(10));

Table created

SQL> INSERT INTO ERROR_LOG VALUES('VAL_001');

1 row inserted

SQL> INSERT INTO ERROR_LOG VALUES('VAL_002');

1 row inserted

SQL> INSERT INTO ERROR_LOG VALUES('VAL_002');

1 row inserted

SQL> INSERT INTO ERROR_LOG VALUES('VAL_003');

1 row inserted

SQL> INSERT INTO ERROR_LOG VALUES('VAL_003');

1 row inserted

SQL> INSERT INTO ERROR_LOG VALUES('VAL_003');

1 row inserted

SQL> INSERT INTO ERROR_LOG VALUES('REM_001');

1 row inserted

SQL> INSERT INTO ERROR_LOG VALUES('REM_001');

1 row inserted

SQL> INSERT INTO ERROR_LOG VALUES('REM_001');

1 row inserted

SQL> INSERT INTO ERROR_LOG VALUES('REM_001');

1 row inserted

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM ERROR_LOG;

ERROR_CODE
----------
VAL_001
VAL_002
VAL_002
VAL_003
VAL_003
VAL_003
REM_001
REM_001
REM_001
REM_001

10 rows selected

SQL> 

SQL> SELECT CNT, ERROR_CODE
  2    FROM (SELECT CNT, ERROR_CODE
  3            FROM (SELECT COUNT(*) CNT, ERROR_CODE
  4                    FROM ERROR_LOG
  5                  GROUP BY ERROR_CODE)
  6          ORDER BY 1 DESC)
  7   WHERE ROWNUM < 4;

       CNT ERROR_CODE
---------- ----------
         4 REM_001
         3 VAL_003
         2 VAL_002

SQL> 

Re: To get top 3 from a table [message #190541 is a reply to message #190539] Thu, 31 August 2006 03:56 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Two similar answers at the same time... Wink
Re: To get top 3 from a table [message #190542 is a reply to message #190541] Thu, 31 August 2006 04:16 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
I hope it's a case of "great minds think alike" rather than an "IBM toilet trained" type reaction Smile
Re: To get top 3 from a table [message #190543 is a reply to message #190542] Thu, 31 August 2006 04:21 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Frank Naude wrote on Thu, 31 August 2006 11:16

I hope it's a case of "great minds think alike" rather than an "IBM toilet trained" type reaction Smile



I totally agree with you: great minds think alike!

Smile
Re: To get top 3 from a table [message #190571 is a reply to message #190543] Thu, 31 August 2006 05:10 Go to previous messageGo to next message
trupti.nanajkar
Messages: 4
Registered: August 2006
Junior Member
WOW!! Thanks guys....

Re: To get top 3 from a table [message #190576 is a reply to message #190571] Thu, 31 August 2006 05:16 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to be aware that if you add another row for error_code val_001 then there are now 4 values sharing the top 3 positions, as VAL_002 and VAL_001 will have the same number of occurrences.
Previous Topic: CAST and MULTICAST
Next Topic: Rank Over
Goto Forum:
  


Current Time: Mon Dec 05 06:46:46 CST 2016

Total time taken to generate the page: 0.11689 seconds