1

For example, if I've got this query:

    select top 10 medication_name, count(1)
    from DimMedical
    group by medication_name
    order by count(1) desc

I can easily get a count of the ten most popular prescriptions.

I can add 'everything else' with a Union like so:

    select top 10 medication_name, count(1)
    from DimMedical
    group by medication_name
    order by count(1) desc
    union
    select 'other', count(1)
    from DimMedical
    where medication_name not in (
                select top 10 medication_name, count(1)
                from DimMedical
                group by medication_name
                order by count(1) desc)

but this hits my table up three times! It's also hard to support, since the same where clause must be in the subquery. It would be easy for the next developer to change one and not the other.

https://www.brentozar.com/pastetheplan/?id=Bk4IqFo9m

Question: What is the best way to get the top N, then everything else?

Sample data (holy whitespace! I've got some normalization to do!):

    create table DimMedical (medication_name varchar(255))

    insert into DimMedical(medication_name)
    values ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('METHOCARBAMOL                      '),
    ('IBUPROFEN                          '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('ZIPSOR                             '),
    ('CELEBREX                           '),
    ('GABAPENTIN                         '),
    ('MELOXICAM                          '),
    ('TRAMADOL HCL                       '),
    ('BACLOFEN                           '),
    ('XODOL                              '),
    ('NAPROXEN                           '),
    ('CELEBREX                           '),
    ('CLONAZEPAM                         '),
    ('OXYCODONE HCL-ACETAMINOPHEN        '),
    ('LYRICA                             '),
    ('LIDODERM                           '),
    ('AMOXICILLIN/CLAVULANATE POTASSIUM  '),
    ('CIPROFLOXACIN                      '),
    ('CLINDAMYCIN HCL                    '),
    ('OXYCODONE HCL-ACETAMINOPHEN        '),
    ('OXYCODONE HCL-ACETAMINOPHEN        '),
    ('LYRICA                             '),
    ('OXYCODONE HCL-ACETAMINOPHEN        '),
    ('CELEBREX                           '),
    ('DICLOFENAC SODIUM                  '),
    ('ACETAMINOPHEN-CODEINE PHOSPHATE    '),
    ('VOLTAREN GEL                       '),
    ('ZIPSOR                             '),
    ('FENTANYL CITRATE                   '),
    ('OPANA ER                           '),
    ('CLARINEX                           '),
    ('NASONEX                            '),
    ('IPRATROPIUM BROMIDE-ALBUTEROL SULFA'),
    ('PROAIR HFA                         '),
    ('ADVAIR DISKUS 250/50               '),
    ('SPIRIVA                            '),
    ('OXYCODONE HCL-ACETAMINOPHEN        '),
    ('IBU                                '),
    ('GABAPENTIN                         '),
    ('NAPROXEN                           '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('TIZANIDINE HCL                     '),
    ('GABAPENTIN                         '),
    ('CYCLOBENZAPRINE HCL                '),
    ('ENDOCET                            '),
    ('ENDOCET                            '),
    ('ZOLPIDEM TARTRATE                  '),
    ('KADIAN                             '),
    ('TRAMADOL HCL                       '),
    ('CYCLOBENZAPRINE HCL                '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('TRAMADOL HCL                       '),
    ('GABAPENTIN                         '),
    ('TRAMADOL HCL                       '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('NAPROXEN                           '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('ZYPREXA                            '),
    ('LEXAPRO                            '),
    ('AMITRIPTYLINE HCL                  '),
    ('CYMBALTA                           '),
    ('MIRTAZAPINE                        '),
    ('LIDODERM                           '),
    ('KADIAN                             '),
    ('MELOXICAM                          '),
    ('CYCLOBENZAPRINE HCL                '),
    ('GABAPENTIN                         '),
    ('TRAZODONE HYDROCHLORIDE            '),
    ('DIAZEPAM                           '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('DEMEROL                            '),
    ('TRAMADOL HCL                       '),
    ('AMITRIPTYLINE HCL                  '),
    ('LITHIUM CARBONATE                  '),
    ('ENDOCET                            '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('Unknown'),
    ('NASONEX                            '),
    ('XOPENEX HFA                        '),
    ('ACETAMINOPHEN-CODEINE PHOSPHATE    '),
    ('MELOXICAM                          '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('OXYCODONE HCL                      '),
    ('OXYCONTIN                          '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('CARISOPRODOL                       '),
    ('OXYCODONE HCL-ACETAMINOPHEN        '),
    ('METHOCARBAMOL                      '),
    ('OXYCONTIN                          '),
    ('AMITRIPTYLINE HCL                  '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('HYDROXYZINE PAMOATE                '),
    ('ALPRAZOLAM                         '),
    ('CARISOPRODOL                       '),
    ('CITALOPRAM HYDROBROMIDE            ')
1
  • 1
    Can you add sample-data and table definition or even better a db<>fiddle (dbfiddle.uk/…) ? – Lennart Oct 10 '18 at 14:29
3

How does this work for you?

SELECT  medication_name, SUM(medication_count) medication_count
FROM    (
    SELECT  CASE WHEN CountOrder < 11 THEN medication_name ELSE 'other' END medication_name, medication_count
    FROM    (
        SELECT  medication_name, COUNT(1) medication_count, ROW_NUMBER() OVER (ORDER BY COUNT(1) DESC) CountOrder
        FROM    DimMedical
        GROUP BY medication_name
    ) subquery2
) subquery1
GROUP BY medication_name
1
  • I like the idea of using a case statement. I prefer to use cte's, though. I've used your suggestion to create a query that only hits the table once (posting it in a minute). – James Oct 10 '18 at 14:52
1

Using Mathewb's suggestion of a case statement and a row_number() function, I've rewritten the query like so. It only hits the table once:

    with ranked_meds as (
    select  medication_name, count(Medication_name) as med_count, rank() over (order by count(Medication_name) desc) as count_rank
    from  dimMedical 
    where category = 'Drugs'
    group by medication_name)

    SELECT case when count_rank < 11 then medication_name else 'other' end as medication_name, sum(med_count) as med_count
    FROM ranked_meds
    group by case when count_rank < 11 then medication_name else 'other' end
    order by sum(med_count) desc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.