A "TOP 10" Query - puzzled as to why this does not work

Question

A "TOP 10" Query - puzzled as to why this does not work

Richard McSharry

Ten Centuries

Points: 1249
More actions
January 19, 2010 at 9:40 am

#209245

<Edit>
This is now solved, but is a useful example of doing a TOP 10 sql query - includes data you can use to test.
<Edit>
Hi,
I have a problem, which I have solved using 2 stored procedures and a cursor, which I wanted to avoid. In the interests of learning, I would love to know if anyone can explain why a correlates subquery does not do what I expected (ie. solve it! :w00t:).
THE PROBLEM
This is an NHS problem related to patient treatments costs for GP's. I need to calculate, for each month of the year, for each General Practice, the top ten highest treatment costs.
STEP 1
First I create a table which contains, for all months, for all practices, the total cost of each treatment.
SELECT DISTINCT
Month_Of_Attendance,
PBC_Practice_Code,
Treatment_Function_Code,
Treatment_Function_Description,
SUM(Total_Cost) as Total_Cost
INTO #TEMP
FROM
tbl_OP_Activity_Costed
GROUP BY
Month_Of_Attendance,
PBC_Practice_Code,
Treatment_Function_Code,
Treatment_Function_Description
ORDER BY
Month_Of_Attendance,
PBC_Practice_Code,
SUM(Total_Cost) DESC
Note that the data is sorted in descending order of cost for each Practice for each month. Therefore if you ran a simple TOP 10 query, it works, like this:
SELECT TOP 10
Total_Cost
FROM
#TEMP as T1
WHERE
T1.Month_Of_Attendance = '200904'
AND
T1.PBC_Practice_Code = 'H84002'
GROUP BY
Month_Of_Attendance,
PBC_Practice_Code,
Treatment_Function_Code,
Treatment_Function_Description,
Total_Cost
2. Using this TOP 10 query as a subquery, I wrote the following code, which does not return the top 10 results for each practice/month:
SELECT
Month_Of_Attendance,
PBC_Practice_Code,
Treatment_Function_Code,
Treatment_Function_Description,
Total_Cost
FROM
#TEMP AS T1
WHERE Total_Cost IN
(SELECT TOP 10
Total_Cost
FROM
#TEMP
WHERE
T1.Month_Of_Attendance = Month_Of_Attendance
AND
T1.PBC_Practice_Code = PBC_Practice_Code
GROUP BY
Month_Of_Attendance,
PBC_Practice_Code,
Treatment_Function_Code,
Treatment_Function_Description,
Total_Cost)
I was under the impression that for each row in the main query, the subquery runs; I know it is not particularly efficient but it's easy to understand.
Can anyone explain to me why this approach does not work?
Also, would it be possible to do this with a CTE rather than a cursor?
Thanks
Richard
PS If you want some data I am happy to post code to create the tables needed.;-)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

The only way to guarantee the order is by using an order by clause. That might have something to do with your current problem. If you would post the table definitions, sample data, and expected results it would help on working through the problem.

Richard McSharry Ten Centuries Points: 1249 More actions · Answer 2

matt6288 (1/19/2010)
The only way to guarantee the order is by using an order by clause.

Thanks for the feedback matt6288. 🙂

That was my suscipion, but I thought that the order was guaranteed by placing the data in the temp table in the order I wanted. Note that the TOP 10 query on its own does work correctly, therefore implying the order is correct.

However, it seems that when used as a correlated subquery, the order the records are retrieved from the table is NOT dependent on the order in which they are stored...but that seems to be inconsistent behaviour to me. :crazy:

Here is the code to get the first two month's of data, which is enough to see the problem:

CREATE TABLE [#TEMP](

[Month_Of_Attendance] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[PBC_Practice_Code] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Treatment_Function_Code] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Treatment_Function_Description] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Total_Cost] [float] NULL

) ON [PRIMARY]

GO

INSERT INTO #TEMP

(Month_Of_Attendance,

PBC_Practice_Code,

Treatment_Function_Code,

Treatment_Function_Description,

Total_Cost)

SELECT '200904','H84002','501','Obstetrics','13235' UNION ALL

SELECT '200904','H84002','110','Trauma & orthopaedic','10882' UNION ALL

SELECT '200904','H84002','120','Ear Nose & Throat','10623' UNION ALL

SELECT '200904','H84002','502','Gynaecology','7099' UNION ALL

SELECT '200904','H84002','130','Ophthalmology','6464' UNION ALL

SELECT '200904','H84002','301','Gastroenterology','5503' UNION ALL

SELECT '200904','H84002','140','Oral Surgery','5163' UNION ALL

SELECT '200904','H84002','420','Paediatric medicine','5034' UNION ALL

SELECT '200904','H84002','370','Medical oncology','4030' UNION ALL

SELECT '200904','H84002','101','Urology','3628' UNION ALL

SELECT '200904','H84002','320','Cardiology','3626' UNION ALL

SELECT '200904','H84002','100','General Surgery','3218' UNION ALL

SELECT '200904','H84002','103','Breast Surgery','2988' UNION ALL

SELECT '200904','H84002','300','General Medicine','2681' UNION ALL

SELECT '200904','H84002','104','Colorectal Surgery','2347' UNION ALL

SELECT '200904','H84002','143','Orthodontics','2183' UNION ALL

SELECT '200904','H84002','160','Plastic Surgery & Burns','1921' UNION ALL

SELECT '200904','H84002','410','Rheumatology','1848' UNION ALL

SELECT '200904','H84002','303','Haematology (clinical)','1726' UNION ALL

SELECT '200904','H84002','503','Gynaecological Oncology','1725' UNION ALL

SELECT '200904','H84002','191','Pain Management','1344' UNION ALL

SELECT '200904','H84002','800','Clinical Oncology','1035' UNION ALL

SELECT '200904','H84002','144','Maxillo-Facial Surgery','917' UNION ALL

SELECT '200904','H84002','340','Thoracic medicine','902' UNION ALL

SELECT '200904','H84002','430','Geriatric medicine','728' UNION ALL

SELECT '200904','H84002','141','Restorative dentistry','725' UNION ALL

SELECT '200904','H84002','302','Endocrinology','642' UNION ALL

SELECT '200904','H84002','107','Kidney Transplantation','529' UNION ALL

SELECT '200904','H84002','105','Hepatobiliary And Pancrec','528' UNION ALL

SELECT '200904','H84002','307','Metabolic Medicine','528' UNION ALL

SELECT '200904','H84002','106','Limb surgery - gen surg','422' UNION ALL

SELECT '200904','H84002','142','Paediatric dentistry','288' UNION ALL

SELECT '200904','H84002','450','Dental Medicine','195' UNION ALL

SELECT '200904','H84002','321','Paediatric Cardiology','152' UNION ALL

SELECT '200905','H84002','501','Obstetrics','13130' UNION ALL

SELECT '200905','H84002','502','Gynaecology','9974' UNION ALL

SELECT '200905','H84002','110','Trauma & orthopaedic','9839' UNION ALL

SELECT '200905','H84002','101','Urology','9710' UNION ALL

SELECT '200905','H84002','120','Ear Nose & Throat','8049' UNION ALL

SELECT '200905','H84002','420','Paediatric medicine','6285' UNION ALL

SELECT '200905','H84002','130','Ophthalmology','6097' UNION ALL

SELECT '200905','H84002','370','Medical oncology','4595' UNION ALL

SELECT '200905','H84002','300','General Medicine','3296' UNION ALL

SELECT '200905','H84002','320','Cardiology','3143' UNION ALL

SELECT '200905','H84002','301','Gastroenterology','3017' UNION ALL

SELECT '200905','H84002','410','Rheumatology','3013' UNION ALL

SELECT '200905','H84002','140','Oral Surgery','2819' UNION ALL

SELECT '200905','H84002','103','Breast Surgery','2668' UNION ALL

SELECT '200905','H84002','143','Orthodontics','1726' UNION ALL

SELECT '200905','H84002','100','General Surgery','1718' UNION ALL

SELECT '200905','H84002','106','Limb surgery - gen surg','1701' UNION ALL

SELECT '200905','H84002','104','Colorectal Surgery','1688' UNION ALL

SELECT '200905','H84002','430','Geriatric medicine','1516' UNION ALL

SELECT '200905','H84002','340','Thoracic medicine','1276' UNION ALL

SELECT '200905','H84002','303','Haematology (clinical)','1228' UNION ALL

SELECT '200905','H84002','142','Paediatric dentistry','1152' UNION ALL

SELECT '200905','H84002','191','Pain Management','1016' UNION ALL

SELECT '200905','H84002','160','Plastic Surgery & Burns','992' UNION ALL

SELECT '200905','H84002','800','Clinical Oncology','828' UNION ALL

SELECT '200905','H84002','321','Paediatric Cardiology','646' UNION ALL

SELECT '200905','H84002','302','Endocrinology','497' UNION ALL

SELECT '200905','H84002','560','Midwife Episode','325' UNION ALL

SELECT '200905','H84002','812','DIAGNOSTIC IMAGING','288' UNION ALL

SELECT '200905','H84002','306','Renal Medicine','266' UNION ALL

SELECT '200905','H84002','307','Metabolic Medicine','264' UNION ALL

SELECT '200905','H84002','105','Hepatobiliary And Pancrec','179' UNION ALL

SELECT '200905','H84002','341','Sleep Studies','109' UNION ALL

SELECT '200905','H84002','171','Paediatric Surgery','91' UNION ALL

SELECT '200905','H84002','503','Gynaecological Oncology','81' UNION ALL

SELECT '200905','H84002','107','Kidney Transplantation','68' UNION ALL

SELECT '200905','H84002','144','Maxillo-Facial Surgery','65' UNION ALL

SELECT '200905','H84002','216','PAEDIATRIC OPHTHALMOLOGY','64'

Richard McSharry Ten Centuries Points: 1249 More actions · Answer 3

I realised that it's not the order that the data is stored in the table that is important - that is only releveant when you are doing a standalone TOP 10 query. For example, the following does not have an order by clause, because the data is already ordered in the #TEMP table:

SELECT TOP 10

Total_Cost

FROM

#TEMP as T1

WHERE

T1.Month_Of_Attendance = '200904'

AND

T1.PBC_Practice_Code = 'H84002'

GROUP BY

Month_Of_Attendance,

PBC_Practice_Code,

Treatment_Function_Code,

Treatment_Function_Description,

Total_Cost

But when using such a TOP 10 query as a correlated subquery, the ORDER BY clause is required:

SELECT

LEFT(Month_Of_Attendance,4) AS YearYYYY,

RIGHT(Month_Of_Attendance,2) AS MonthMM,

PBC_Practice_Code,

Treatment_Function_Code,

Treatment_Function_Description,

Total_Cost

FROM

#TEMP AS T1

WHERE

Total_Cost IN

(SELECT TOP 10

Total_Cost

FROM

#TEMP

WHERE

T1.Month_Of_Attendance = Month_Of_Attendance

AND

T1.PBC_Practice_Code = PBC_Practice_Code

GROUP BY

Month_Of_Attendance,

PBC_Practice_Code,

Treatment_Function_Code,

Treatment_Function_Description,

Total_Cost

ORDER BY Total_Cost DESC)

That last line is the crucial one.

Thanks for reading!

Richard

PS Here's another great puzzle:

"Why does posting a question to forums so often result in the answer popping into one's head immediately after posting?" :ermm:

Lowell SSC Guru Points: 323518 More actions · Answer 4

Richard McSharry (1/19/2010)
"Why does posting a question to forums so often result in the answer popping into one's head immediately after posting?" :ermm:

So true!

by the time you take the time to write out the explaination of the problem so someone else can understand it, you've gone over the issue in a different perspective, and that often helps you reorganize your thinking so you already know the answer.

I can think of at least a couple dozen times i've started to post a question, and by the time i add the scripts so someone else can duplicate my steps, i see the answer. I end up not bothering to post the question because of it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 5

Richard McSharry (1/19/2010)
PS Here's another great puzzle:
"Why does posting a question to forums so often result in the answer popping into one's head immediately after posting?" :ermm:

BWAA-HAA!!! For the same reason that it rains right after you wash your car or your cell phone rings right after you stuff your mouth full of food. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

Richard McSharry (1/19/2010)
THE PROBLEM
This is an NHS problem related to patient treatments costs for GP's. I need to calculate, for each month of the year, for each General Practice, the top ten highest treatment costs.

Nice to know where my taxes are going

Clear Sky SQL
My Blog[/url]

Richard McSharry Ten Centuries Points: 1249 More actions · Answer 7

Dave Ballantyne (1/20/2010)
Richard McSharry (1/19/2010)
THE PROBLEM
This is an NHS problem related to patient treatments costs for GP's. I need to calculate, for each month of the year, for each General Practice, the top ten highest treatment costs.
Nice to know where my taxes are going

Well yes, but I think the bankers have more of your taxes nowadays!

...I would like to claim tat we need to know which are the top ten highest cost treatments so we can make the bankers pay for their own! lol