Sql-server – What’s the best way to get the top N, then everything else as n + 1

sql serversql-server-2008

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

Best Answer

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
Related Question