February 1, 2011 at 4:02 am
If I have a query like-
select top(100)1,2,3,4
How can I reorganize the final product so that the columns are-
3,4,2,1?
I want the "bill_code_cnt" column to be 4th, but cannot reorder the 'top' select statement. Do I have to do another subquery with it and then order it in my new select statement accordingly? Thanks:
declare @sd as datetime
declare @ed as datetime
set @sd = '2010-07-01 00:00:01'
set @ed = '2011-01-31 23:59:59'
select top(400)bill_code_cnt,d.DrugID,d.GenericName,bill_code
from
(SELECT X.bill_code, X.bill_code_cnt,
DENSE_RANK() OVER (ORDER BY X.bill_code_cnt) AS bill_code_standing
FROM (SELECT RX.BillCode, COUNT(*) AS bill_code_cnt
FROM PhaRx AS RX JOIN AdmVisits AS AV
ON RX.VisitID = AV.VisitID
WHERE RX.BillCode IS NOT NULL
and convert(char,RX.EnterDateTime,120) between @sd AND @ed
and RX.Inventory not in ('PX-OR','PX-GIDA','PX-DSPOR','PX-PRECATH','PX-CATHL9','PX-CATHL8','PX-CATHL7','PX-CATHL6','PX-CATHL5'
,'PX-CATHL4','PX-CATHL3','PX-CATHL2','PX-CATHL1','PX-ER','PX-PREAN')
and RX.Inventory not like '%KIT%'
and RX.Inventory not like '%STK%'
GROUP BY RX.BillCode)
AS X(bill_code, bill_code_cnt)) r
join DPhaDrugData d on d.BillNumber = r.bill_code
where d.Active = 'Y'
and d.UsageType like 'Formulary'
group by DrugID,GenericName,bill_code,r.bill_code_cnt
ORDER BY r.bill_code_cnt DESC
February 1, 2011 at 4:05 am
The column order has nothing to do with the TOP operator.
You can just simply reorder the columns in your select statement , though , IMO, relying on column order anywhere is wrong....
February 1, 2011 at 4:36 am
But I cannot do "select 1,2,3,top(100)4"...
The 'top' portion has to be #1 in the select statement, no?
At least when I try to do "select 1,2,3,top(100)4" it gives me an 'incorrect syntax' error by "top".
February 1, 2011 at 4:50 am
The TOP clause is in direct relevance to the order by clause
in your case
ORDER BY r.bill_code_cnt DESC
So it doesent matter if you
Select top(100) 1,2,3,4
or
Select top(100) 4,3,2,1
The top 100 rows will be the same top 100 rows (baring duplicates on r.bill_code_cnt)
February 1, 2011 at 5:30 am
I did not realize the order didn't matter in the 'top' select statement.
I guess I just don't know data mining that well (obviously I'm a newb in the newb forum), but I thought "top1,2" would have a different set of info then "top2,1", but it didn't when I changed it as you said.
I just never thought it worked that way, thanks!
February 1, 2011 at 5:50 am
pharmboy4u (2/1/2011)
If I have a query like-select top(100)1,2,3,4
How can I reorganize the final product so that the columns are-
3,4,2,1?
I want the "bill_code_cnt" column to be 4th, but cannot reorder the 'top' select statement. Do I have to do another subquery with it and then order it in my new select statement accordingly? Thanks:
No, the code is already unnecessarily complicated, try this:
SELECT TOP (400) -- ORDER BY r.bill_code_cnt DESC - will output the 400 rows with highest r.bill_code_cnt
r.bill_code_cnt,
d.DrugID,
d.GenericName,
r.bill_code
FROM DPhaDrugData d
INNER JOIN (
SELECT
RX.BillCode,
COUNT(*) AS bill_code_cnt
FROM PhaRx AS RX
JOIN AdmVisits AS AV
ON RX.VisitID = AV.VisitID
WHERE RX.BillCode IS NOT NULL
AND convert(char,RX.EnterDateTime,120) between @sd AND @ed -- adjust datatype of @sd AND @ed to match RX.EnterDateTime and remove CAST
-- replacing long lists like this with a #temporary table can improve performance and will improve readability
AND RX.Inventory NOT IN ('PX-OR','PX-GIDA','PX-DSPOR','PX-PRECATH','PX-CATHL9','PX-CATHL8','PX-CATHL7','PX-CATHL6','PX-CATHL5'
,'PX-CATHL4','PX-CATHL3','PX-CATHL2','PX-CATHL1','PX-ER','PX-PREAN')
AND RX.Inventory NOT LIKE '%KIT%'
AND RX.Inventory NOT LIKE '%STK%'
GROUP BY RX.BillCode
) r
ON r.bill_code = d.BillNumber
WHERE d.Active = 'Y'
AND d.UsageType LIKE 'Formulary' -- this is equivalent to =, you need % or other wildcard for LIKE to work
GROUP BY d.DrugID, d.GenericName, r.bill_code, r.bill_code_cnt
ORDER BY r.bill_code_cnt DESC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply