Ordering column data while using 'top' fxn

  • 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

  • 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....



    Clear Sky SQL
    My Blog[/url]

  • 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".

  • 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)



    Clear Sky SQL
    My Blog[/url]

  • 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!

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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