By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,225 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

How to select Highest 5 counts

P: 2
Hi,

I have a table like the following:

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
Feb 24 '07 #1
Share this Question
Share on Google+
3 Replies


P: 2
Hi,

I just wrote a query that select all the records where DRG=79 and it also lists
the count for each code. Now I need to know how I can select the highest 5 codes and list them in DESC order. Here is the code I have so far:

SELECT A.Dx1, Count(*) AS [counter]
FROM table1 AS A
WHERE A.DRG=79
GROUP BY A.DC_Dx1;
Feb 24 '07 #2

ADezii
Expert 5K+
P: 8,597
Hi,

I have a table like the following:

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
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 5 Table1.DX1, Table1.DX1_DESC, Table1.DRG
  2. FROM Table1
  3. WHERE Table1.DRG=79
  4. ORDER BY Table1.DX1 DESC;
Feb 25 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
Try this version :
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 5 DX1,Counter
  2. FROM (SELECT Dx1,Count(*) AS [Counter]
  3.       FROM Table1
  4.       WHERE DRG=79
  5.       GROUP BY DX1) AS subQ
  6. ORDER BY [Counter] DESC
Feb 26 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.