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
3 1981
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;
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
- SELECT TOP 5 Table1.DX1, Table1.DX1_DESC, Table1.DRG
-
FROM Table1
-
WHERE Table1.DRG=79
-
ORDER BY Table1.DX1 DESC;
NeoPa 32,556
Expert Mod 16PB
Try this version : - SELECT TOP 5 DX1,Counter
-
FROM (SELECT Dx1,Count(*) AS [Counter]
-
FROM Table1
-
WHERE DRG=79
-
GROUP BY DX1) AS subQ
-
ORDER BY [Counter] DESC
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mark |
last post by:
good spam subject ;).
anyway, i'm alittle stumped. i'm in need of putting together a query
that gets the next highest salary ( select max ( sal ) - 1?, from an
emp_sal type table. another...
|
by: William Wisnieski |
last post by:
Hello Again,
I'm really stuck on this one.....so I'm going to try a different approach to
this problem.
I have a query by form that returns a record set in a datasheet. The user
double...
|
by: Neo Geshel |
last post by:
I have an Access DB, from which I am going to pull images. Each image
has an associated ID, but the ID's are not necessarily sequential (some
images may have been deleted, leaving gaps in the list...
|
by: johnk |
last post by:
I have a table of items, with revision numbers. I need to extract the
items with highest revision number. The items may be listed several
times and I don't know what the highest revision number...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: Matt Chwastek |
last post by:
Anyone who can help,
I am curretnly attempting to write some code that will allow iteration
using a vector<intfrom the highest possilbe degree of a combination
of ones & zeros (111, 110, 101,...
|
by: fperri |
last post by:
Hello,
I have a query that creates a table of duplicates in my database. For one set of duplicates (three records), all the fields & values are the same except for the values in one field. This...
|
by: Carroll |
last post by:
I would like to be able to select the maximum (highest ) value for a
field, the 2nd highest, 3rd highest, etc. Are there any other
functions that I might consider using with SQL? MAX will only...
|
by: runway27 |
last post by:
hi
i have an enquiry table which collects information about users
making an online travel enquiry
the fields in the table are = StoryTitle, EndCity, mode, PricedFrom,
numAdults, numChildren,...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |