Select top 5 by SQL not using "top"

[JPCogAdmin]

Hi everyone,

I really need to get some help.
Here is the scenario:


I have a table with appro. 65 columns in MS-Access (eventually this will be in Oracle). Anyway, I need to select the top 5 categories. In other scenarios I selected the top 10 categories using the following code (portable in any db):

=====================================================
SELECT A, visits, C, D, E
FROM table1 AS first
WHERE 10 > (select count(*) from table1 second where first.Visits < second.Visits)
ORDER BY Visits DESC;
=====================================================

Now I need to select the top 5 categories using a column whose values range in the following format:

507
11.93
510.9
11.9 and so on.....

How can I possible create this query? Can one of you help me? I thank you in advance..

-J

 

[AlexCuse]

And you want the top five highest or lowest?

something like this could work (for lowest, would require some tweaking to get highest)

select a.CatCol
, a.OtherCol
, count(b.CatCol)
from myTable a
left join myTable b
on a.CatCol >=b.CatCol
group by a.CatCol
having count(b.CatCol) <= 5

This assumes that you want either the top 5 lowest or top 5 highest numbers. The count displayed will be 1 for the absolute lowest, 5 for the 5th lowest.

To do the same thing for the 5 highest, you could do this:

select a.CatCol
, a.OtherCol
, count(b.CatCol)
from myTable a
left join myTable b
on a.CatCol <=b.CatCol
group by a.CatCol
having count(b.CatCol) <= 5

One note, catCol is the column you gave the sample decimal values for.

I hope there are no more typos. I tested this in MS Access.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[r937]

ms access won't let you do this, though --

select a.CatCol
, a.OtherCol
...
group by a.CatCol



r937.com | rudy.ca

 

[AlexCuse]

Yeah I just added that after I posted (only tested on the one column), and like a dope forgot to add it to the group by . I thought of it last night, but it was a little late to get back on. I thin you pointed it out quickly enough that jpCogAdmin will be able to get it to work.

Muchas Gracias Rudy!

Ignorance of certain subjects is a great part of wisdom

 

[JPCogAdmin]

AlexCuse,

Thanks alot. I'm running the query right....
It is taking quite a while, don't know why but it seems to be working on something. It finished just now and it only gave me 1 record.

So here is a sampling of the data:

TableName = Table1

DX1, DX1_DESC, DRG

The values from the 3 columns looks like the following

011.24 , abc , 79
434.91 , ytw , 79
434.91 , ytw , 79
574.71 , xyw , 79
574.60 , poi , 79
574.70 , ter , 70
011.04 , wsx , 79
011.24 , abc , 79
011.24 , abc , 79
59 , yyy , 79
59 , yyy , 79
574.71 , xyw , 79
574.71 , xyw , 79
574.71 , xyw , 79
574.60 , poi , 79
574.60 , poi , 79
574.60 , poi , 79
574.71 , xyw , 79
574.71 , xyw , 88
59 , yyy , 79
574.71 , xyw , 79
011.24 , abc , 79
011.24 , abc , 79
59 , yyy , 79
59 , yyy , 79

Now, how can I select the highest 5 count from the DX1 field and DRG=79 to get a result like the following below:

DX1 , DX1_DESC , CASES
574.71 ---------- xyw ---------- 6
59 ---------- yyy ---------- 5
011.24 ---------- abc ---------- 4
574.60 ---------- poi ---------- 3
434.91 ---------- ytw ---------- 2

I'm using the "------" to squeeze in space for the columns in this posting.
Thank you very much for your help.



-J

 

[r937]

this works (i tested it on your sample data) --

select DX1
     , DX1_DESC
     , c
  from (
       select DX1          
            , DX1_DESC     
            , count(*) as c
         from JP           
        where DRG=79       
       group               
           by DX1          
            , DX1_DESC    
       ) as T
 where (
       select count(*)
         from (
              select DX1          
                   , DX1_DESC     
                   , count(*) as c
                from JP           
               where DRG=79       
              group               
                  by DX1          
                   , DX1_DESC  
              ) as T1
        where c > T.c ) < 5
order
    by c desc

i believe the following query using a common table expression will also work --

with T 
     ( DX1
     , DX1_DESC
     , c )
as (
select DX1
     , DX1_DESC
     , count(*) as c
  from JP
 where DRG=79
group
    by DX1
     , DX1_DESC 
   )
select DX1
     , DX1_DESC
     , c 
  from T
 where (
       select count(*)
         from T as T1
        where c > T.c ) < 5

r937.com | rudy.ca

 

[JPCogAdmin]

Hi r937,

Wow it works.......
;-)
I'm very happy.......
;-)
Thank you so much for helping me. AlexCuse, thank you also for helping me. Your response started everything else for me here. I'm really very very appreciative of this.

One more question, I have just noticed that the table also has a column named Count and in it there is a value of "1" for all the listed records. Could that field have made it alot easier? If so, how could another way using that field have been created?

JP

 

[r937]

that field would not have helped

r937.com | rudy.ca

 

[AlexCuse]

Count will not work for you, especially if they are all = 1. It sounds like there was an idea at some time to store the count based on some set of grouping criteria, but maybe the steps to perform these updates were not ever implemented?

Better to calculate what you know is the current count in your query if you ask me



Ignorance of certain subjects is a great part of wisdom

 

[JPCogAdmin]

Thank you to both of you and to rudy.ca as well.
you guys have put a humongous smile on my face and .....

Wow, I'm still taking it in...
hehe.

 

[JPCogAdmin]

r937 and alexCuse,

can you recommend a specific book on SQL that could help in the future? Also r937 in the SQL code is T1 ever used?

-J

 

[r937]

SQL for Smarties, 3rd edition, Joe Celko

T1 is needed to distinguish from T ;-)

r937.com | rudy.ca