May 23, 2017 at 12:25 pm
I need to get the top 15 diags from this table ranked so that the ranks are not repeated. So, there should be 15 distinct Diags but, may have several records for each Diag. I need the VisitID included in the results.
create table #T(VisitID varchar(30),Diag varchar(100))insert into #T(VisitID, Diag) values('F0-B20170118132856995','S09.90XA - Unspecified injury of head, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170117115126266','D68.32 - Hemorrhagic disorder due to extrinsic circulating anticoagulants')insert into #T(VisitID, Diag) values('F1-B20170131125857621','K76.7 - Hepatorenal syndrome')insert into #T(VisitID, Diag) values('F1-B20170223200345467','S82.391A - Other fracture of lower end of right tibia, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170507012147561','I62.9 - Nontraumatic intracranial hemorrhage, unspecified')insert into #T(VisitID, Diag) values('F1-B20170430090539414','S01.511A - Laceration without foreign body of lip, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170101222116896','M48.56XA - Collapsed vertebra, not elsewhere classified, lumbar region, initial encounter for fractu~')insert into #T(VisitID, Diag) values('F1-B20170103224636495','R55 - Syncope and collapse')insert into #T(VisitID, Diag) values('F1-B20170228164721489','K74.60 - Unspecified cirrhosis of liver')insert into #T(VisitID, Diag) values('F1-B20170305145616651','N83.8 - Other noninflammatory disorders of ovary, fallopian tube and broad ligament')insert into #T(VisitID, Diag) values('F0-B20170409203409870','I26.99 - Other pulmonary embolism without acute cor pulmonale')insert into #T(VisitID, Diag) values('F0-B20170122150320460','C43.9 - Malignant melanoma of skin, unspecified')insert into #T(VisitID, Diag) values('F1-B20170108141452158','L53.9 - Erythematous condition, unspecified')insert into #T(VisitID, Diag) values('F1-B20170402002257042','T38.3X2A - Poisoning by insulin and oral hypoglycemic [antidiabetic] drugs, intentional self-harm, i~')insert into #T(VisitID, Diag) values('F0-B20170511072447007','I62.9 - Nontraumatic intracranial hemorrhage, unspecified')insert into #T(VisitID, Diag) values('F0-B20170214091330413','N39.0 - Urinary tract infection, site not specified')insert into #T(VisitID, Diag) values('F0-B20170213023455639','G93.9 - Disorder of brain, unspecified')insert into #T(VisitID, Diag) values('F1-B20170102070448718','J44.1 - Chronic obstructive pulmonary disease with (acute) exacerbation')insert into #T(VisitID, Diag) values('F1-B20170101130614603','J21.9 - Acute bronchiolitis, unspecified')insert into #T(VisitID, Diag) values('F1-B20170421021702248','K92.2 - Gastrointestinal hemorrhage, unspecified')insert into #T(VisitID, Diag) values('F1-B20170126131645263','R19.00 - Intra-abdominal and pelvic swelling, mass and lump, unspecified site')insert into #T(VisitID, Diag) values('F1-B20170420014929282','M54.12 - Radiculopathy, cervical region')insert into #T(VisitID, Diag) values('F0-B20170412102138971','S06.4X2A - Epidural hemorrhage with loss of consciousness of 31 minutes to 59 minutes, initial encou~')insert into #T(VisitID, Diag) values('F1-B20170303121000569','R55 - Syncope and collapse')insert into #T(VisitID, Diag) values('F1-B20170225233157775','F03.90 - Unspecified dementia without behavioral disturbance')insert into #T(VisitID, Diag) values('F1-B20170408092502028','L30.9 - Dermatitis, unspecified')insert into #T(VisitID, Diag) values('F0-B20170313102801011','K86.89 - Other specified diseases of pancreas')insert into #T(VisitID, Diag) values('F0-B20170318014646229','M96.830 - Postprocedural hemorrhage of a musculoskeletal structure following a musculoskeletal syst~')insert into #T(VisitID, Diag) values('F0-B20170326024521351','S02.2XXB - Fracture of nasal bones, initial encounter for open fracture')insert into #T(VisitID, Diag) values('F1-B20170401075239398','R33.9 - Retention of urine, unspecified')insert into #T(VisitID, Diag) values('F1-B20170427062542528','N83.512 - Torsion of left ovary and ovarian pedicle')insert into #T(VisitID, Diag) values('F1-B20170423212455971','G44.89 - Other headache syndrome')insert into #T(VisitID, Diag) values('F1-B20170213152535911','S01.511A - Laceration without foreign body of lip, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170325175701168','M43.16 - Spondylolisthesis, lumbar region')insert into #T(VisitID, Diag) values('F0-B20170328210009112','K56.60 - Unspecified intestinal obstruction')insert into #T(VisitID, Diag) values('F0-B20170424195237383','I63.9 - Cerebral infarction, unspecified')insert into #T(VisitID, Diag) values('F0-B20170505141247094','S02.40CA - Maxillary fracture, right side, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F1-B20170116105852503','J18.1 - Lobar pneumonia, unspecified organism')insert into #T(VisitID, Diag) values('F1-B20170419205051543','S42.212A - Unspecified displaced fracture of surgical neck of left humerus, initial encounter for cl~')insert into #T(VisitID, Diag) values('F0-B20170131230940731','E13.10 - Other specified diabetes mellitus with ketoacidosis without coma')insert into #T(VisitID, Diag) values('F1-B20170228154552239','S06.300A - Unspecified focal traumatic brain injury without loss of consciousness, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170411104504961','T50.992A - Poisoning by other drugs, medicaments and biological substances, intentional self-harm, i~')insert into #T(VisitID, Diag) values('F0-B20170218114727463','R55 - Syncope and collapse')insert into #T(VisitID, Diag) values('F0-B20170220132416592','R07.9 - Chest pain, unspecified')insert into #T(VisitID, Diag) values('F1-B20170220114311697','K56.60 - Unspecified intestinal obstruction')insert into #T(VisitID, Diag) values('F0-B20170311014603016','I48.91 - Unspecified atrial fibrillation')insert into #T(VisitID, Diag) values('F0-B20170310232925483','J44.1 - Chronic obstructive pulmonary disease with (acute) exacerbation')insert into #T(VisitID, Diag) values('F1-B20170106101011370','D70.1 - Agranulocytosis secondary to cancer chemotherapy')insert into #T(VisitID, Diag) values('F0-B20170113110132236','I63.9 - Cerebral infarction, unspecified')insert into #T(VisitID, Diag) values('F1-B20170329104439758','F10.129 - Alcohol abuse with intoxication, unspecified')insert into #T(VisitID, Diag) values('F1-B20170404203317555','R42 - Dizziness and giddiness')insert into #T(VisitID, Diag) values('F0-B20170518163519838','R06.00 - Dyspnea, unspecified')insert into #T(VisitID, Diag) values('F1-B20170424113626293','G40.909 - Epilepsy, unspecified, not intractable, without status epilepticus')insert into #T(VisitID, Diag) values('F0-B20170101091716270','S32.402A - Unspecified fracture of left acetabulum, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170515145601319','S82.52XA - Displaced fracture of medial malleolus of left tibia, initial encounter for closed fractu~')insert into #T(VisitID, Diag) values('F0-B20170421105622392','K42.0 - Umbilical hernia with obstruction, without gangrene')insert into #T(VisitID, Diag) values('F0-B20170131212517602','A41.9 - Sepsis, unspecified organism')insert into #T(VisitID, Diag) values('F1-B20170509193438787','I26.99 - Other pulmonary embolism without acute cor pulmonale')insert into #T(VisitID, Diag) values('F0-B20170511100231816','R10.9 - Unspecified abdominal pain')insert into #T(VisitID, Diag) values('F0-B20170509171002968','S52.181A - Other fracture of upper end of right radius, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F1-B20170416190700639','I61.8 - Other nontraumatic intracerebral hemorrhage')insert into #T(VisitID, Diag) values('F1-B20170201153907065','S02.2XXA - Fracture of nasal bones, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170204085739209','S02.641A - Fracture of ramus of right mandible, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170412102608718','G83.4 - Cauda equina syndrome')insert into #T(VisitID, Diag) values('F1-B20170409023053284','R19.00 - Intra-abdominal and pelvic swelling, mass and lump, unspecified site')insert into #T(VisitID, Diag) values('F1-B20170330085331969','I61.0 - Nontraumatic intracerebral hemorrhage in hemisphere, subcortical')insert into #T(VisitID, Diag) values('F1-B20170327122003662','K56.69 - Other intestinal obstruction')insert into #T(VisitID, Diag) values('F0-B20170327072938479','M54.42 - Lumbago with sciatica, left side')insert into #T(VisitID, Diag) values('F0-B20170115100546731','B30.0 - Keratoconjunctivitis due to adenovirus')insert into #T(VisitID, Diag) values('F0-B20170515171327595','S06.5X0A - Traumatic subdural hemorrhage without loss of consciousness, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170119115543539','S32.019A - Unspecified fracture of first lumbar vertebra, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170403120640348','T82.868A - Thrombosis due to vascular prosthetic devices, implants and grafts, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170418210017707','I71.01 - Dissection of thoracic aorta')insert into #T(VisitID, Diag) values('F1-B20170205133752891','S09.90XA - Unspecified injury of head, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170316131505040','R55 - Syncope and collapse')insert into #T(VisitID, Diag) values('F0-B20170310202540644','S42.291A - Other displaced fracture of upper end of right humerus, initial encounter for closed frac~')insert into #T(VisitID, Diag) values('F1-B20170301210849050','S02.2XXA - Fracture of nasal bones, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170504150322973','N48.89 - Other specified disorders of penis')insert into #T(VisitID, Diag) values('F1-B20170115195342977','J10.1 - Influenza due to other identified influenza virus with other respiratory manifestations')insert into #T(VisitID, Diag) values('F0-B20170128180505854','R45.851 - Suicidal ideations')insert into #T(VisitID, Diag) values('F0-B20170409101750739','R50.9 - Fever, unspecified')insert into #T(VisitID, Diag) values('F1-B20170318203504710','R20.9 - Unspecified disturbances of skin sensation')insert into #T(VisitID, Diag) values('F1-B20170305081925117','T78.40XA - Allergy, unspecified, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170111102517509','R07.9 - Chest pain, unspecified')insert into #T(VisitID, Diag) values('F0-B20170105122222444','M48.54XA - Collapsed vertebra, not elsewhere classified, thoracic region, initial encounter for frac~')insert into #T(VisitID, Diag) values('F0-B20170512183110063','N10 - Acute pyelonephritis')insert into #T(VisitID, Diag) values('F0-B20170507225539106','S02.40EA - Zygomatic fracture, right side, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170515061213420','J18.9 - Pneumonia, unspecified organism')insert into #T(VisitID, Diag) values('F1-B20170518130348935','K91.89 - Other postprocedural complications and disorders of digestive system')insert into #T(VisitID, Diag) values('F0-B20170516200758345','I26.99 - Other pulmonary embolism without acute cor pulmonale')insert into #T(VisitID, Diag) values('F0-B20170516113132669','K55.059 - Acute (reversible) ischemia of intestine, part and extent unspecified')insert into #T(VisitID, Diag) values('F0-B20170421211507128','R07.89 - Other chest pain')insert into #T(VisitID, Diag) values('F1-B20170207110442475','J90 - Pleural effusion, not elsewhere classified')insert into #T(VisitID, Diag) values('F1-B20170129184820496','K76.7 - Hepatorenal syndrome')insert into #T(VisitID, Diag) values('F0-B20170228213106403','R10.9 - Unspecified abdominal pain')insert into #T(VisitID, Diag) values('F1-B20170408205743859','M54.5 - Low back pain')insert into #T(VisitID, Diag) values('F1-B20170123123224550','G91.9 - Hydrocephalus, unspecified')insert into #T(VisitID, Diag) values('F1-B20170109172343897','D64.89 - Other specified anemias')insert into #T(VisitID, Diag) values('F1-B20170319142126127','G40.409 - Other generalized epilepsy and epileptic syndromes, not intractable, without status epile~')insert into #T(VisitID, Diag) values('F1-B20170326163257092','I62.00 - Nontraumatic subdural hemorrhage, unspecified')insert into #T(VisitID, Diag) values('F1-B20170403133412525','I62.9 - Nontraumatic intracranial hemorrhage, unspecified')insert into #T(VisitID, Diag) values('F1-B20170504140133851','S02.82XA - Fracture of other specified skull and facial bones, left side, initial encounter for clos~')insert into #T(VisitID, Diag) values('F0-B20170213102226441','I62.9 - Nontraumatic intracranial hemorrhage, unspecified')insert into #T(VisitID, Diag) values('F0-B20170515093028383','R47.01 - Aphasia')insert into #T(VisitID, Diag) values('F1-B20170421143735758','D18.02 - Hemangioma of intracranial structures')insert into #T(VisitID, Diag) values('F0-B20170128110220846','O26.891 - Other specified pregnancy related conditions, first trimester')insert into #T(VisitID, Diag) values('F0-B20170420055621282','H11.31 - Conjunctival hemorrhage, right eye')insert into #T(VisitID, Diag) values('F0-B20170303162100490','R41.82 - Altered mental status, unspecified')insert into #T(VisitID, Diag) values('F1-B20170109034959511','S01.112A - Laceration without foreign body of left eyelid and periocular area, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170331171541524','I62.01 - Nontraumatic acute subdural hemorrhage')insert into #T(VisitID, Diag) values('F0-B20170520030056285','R10.13 - Epigastric pain')insert into #T(VisitID, Diag) values('F0-B20170505203637968','I61.8 - Other nontraumatic intracerebral hemorrhage')insert into #T(VisitID, Diag) values('F0-B20170206133651847','N18.3 - Chronic kidney disease, stage 3 (moderate)')insert into #T(VisitID, Diag) values('F1-B20170211105327981','I60.9 - Nontraumatic subarachnoid hemorrhage, unspecified')insert into #T(VisitID, Diag) values('F1-B20170511103447989','T21.21XA - Burn of second degree of chest wall, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170131091743119','K92.2 - Gastrointestinal hemorrhage, unspecified')insert into #T(VisitID, Diag) values('F1-B20170227134836715','K92.2 - Gastrointestinal hemorrhage, unspecified')insert into #T(VisitID, Diag) values('F1-B20170226185900858','S06.9X0A - Unspecified intracranial injury without loss of consciousness, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170222125135096','F19.10 - Other psychoactive substance abuse, uncomplicated')insert into #T(VisitID, Diag) values('F0-B20170120094543397','I63.031 - Cerebral infarction due to thrombosis of right carotid artery')insert into #T(VisitID, Diag) values('F1-B20170112131052569','S22.069A - Unspecified fracture of T7-T8 vertebra, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170505231837672','K68.12 - Psoas muscle abscess')insert into #T(VisitID, Diag) values('F0-B20170519171339387','F05 - Delirium due to known physiological condition')insert into #T(VisitID, Diag) values('F0-B20170520145725548','T85.71XA - Infection and inflammatory reaction due to peritoneal dialysis catheter, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170117164546832','S82.042A - Displaced comminuted fracture of left patella, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170505014748951','T79.A11A - Traumatic compartment syndrome of right upper extremity, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170501181546058','I63.9 - Cerebral infarction, unspecified')insert into #T(VisitID, Diag) values('F1-B20170514183729099','T23.502A - Corrosion of first degree of left hand, unspecified site, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170510180624375','S06.5X0A - Traumatic subdural hemorrhage without loss of consciousness, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170212204038774','S01.81XA - Laceration without foreign body of other part of head, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170126140202642','S02.612G - Fracture of condylar process of left mandible, subsequent encounter for fracture with del~')insert into #T(VisitID, Diag) values('F0-B20170128160350349','I63.9 - Cerebral infarction, unspecified')insert into #T(VisitID, Diag) values('F1-B20170407172743903','G03.9 - Meningitis, unspecified')insert into #T(VisitID, Diag) values('F0-B20170123192700689','G89.18 - Other acute postprocedural pain')insert into #T(VisitID, Diag) values('F0-B20170107154330223','M54.16 - Radiculopathy, lumbar region')insert into #T(VisitID, Diag) values('F0-B20170325203222006','G89.18 - Other acute postprocedural pain')insert into #T(VisitID, Diag) values('F0-B20170329145425100','G97.82 - Other postprocedural complications and disorders of nervous system')insert into #T(VisitID, Diag) values('F1-B20170326142025219','M54.16 - Radiculopathy, lumbar region')insert into #T(VisitID, Diag) values('F0-B20170320131016584','R51 - Headache')insert into #T(VisitID, Diag) values('F0-B20170506171420347','R51 - Headache')insert into #T(VisitID, Diag) values('F1-B20170114000531513','O26.893 - Other specified pregnancy related conditions, third trimester')insert into #T(VisitID, Diag) values('F1-B20170213100123324','J10.1 - Influenza due to other identified influenza virus with other respiratory manifestations')insert into #T(VisitID, Diag) values('F0-B20170508211839774','G40.909 - Epilepsy, unspecified, not intractable, without status epilepticus')insert into #T(VisitID, Diag) values('F0-B20170511175756601','S42.415A - Nondisplaced simple supracondylar fracture without intercondylar fracture of left humerus~')insert into #T(VisitID, Diag) values('F1-B20170416161258000','M54.5 - Low back pain')insert into #T(VisitID, Diag) values('F0-B20170130050228215','J18.9 - Pneumonia, unspecified organism')insert into #T(VisitID, Diag) values('F0-B20170420190717444','R07.9 - Chest pain, unspecified')insert into #T(VisitID, Diag) values('F1-B20170216173406150','F03.91 - Unspecified dementia with behavioral disturbance')insert into #T(VisitID, Diag) values('F0-B20170304174754553','S02.19XB - Other fracture of base of skull, initial encounter for open fracture')insert into #T(VisitID, Diag) values('F1-B20170307131842707','S72.122A - Displaced fracture of lesser trochanter of left femur, initial encounter for closed fract~')insert into #T(VisitID, Diag) values('F0-B20170219121519245','I63.9 - Cerebral infarction, unspecified')insert into #T(VisitID, Diag) values('F0-B20170408114202063','S01.81XA - Laceration without foreign body of other part of head, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170408102642811','S05.01XA - Injury of conjunctiva and corneal abrasion without foreign body, right eye, initial encou~')insert into #T(VisitID, Diag) values('F1-B20170213120749453','R07.9 - Chest pain, unspecified')insert into #T(VisitID, Diag) values('F0-B20170322175255144','T83.83XA - Hemorrhage due to genitourinary prosthetic devices, implants and grafts, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170327211511366','R41.82 - Altered mental status, unspecified')insert into #T(VisitID, Diag) values('F0-B20170201191230477','G40.901 - Epilepsy, unspecified, not intractable, with status epilepticus')insert into #T(VisitID, Diag) values('F0-B20170316120504432','M54.16 - Radiculopathy, lumbar region')insert into #T(VisitID, Diag) values('F1-B20170331114016523','J36 - Peritonsillar abscess')insert into #T(VisitID, Diag) values('F0-B20170121130818494','K51.90 - Ulcerative colitis, unspecified, without complications')insert into #T(VisitID, Diag) values('F0-B20170123110419544','S09.90XA - Unspecified injury of head, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170317230353860','S82.232A - Displaced oblique fracture of shaft of left tibia, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170322183210509','N39.0 - Urinary tract infection, site not specified')insert into #T(VisitID, Diag) values('F0-B20170507140441088','S27.0XXA - Traumatic pneumothorax, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170210160132579','I69.320 - Aphasia following cerebral infarction')insert into #T(VisitID, Diag) values('F1-B20170421154507816','S06.5X0A - Traumatic subdural hemorrhage without loss of consciousness, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170127210312351','N50.89 - Other specified disorders of the male genital organs')insert into #T(VisitID, Diag) values('F0-B20170128131516738','G93.40 - Encephalopathy, unspecified')insert into #T(VisitID, Diag) values('F1-B20170408091759931','I71.3 - Abdominal aortic aneurysm, ruptured')insert into #T(VisitID, Diag) values('F0-B20170311140014407','H60.12 - Cellulitis of left external ear')insert into #T(VisitID, Diag) values('F0-B20170402155004675','S06.5X9A - Traumatic subdural hemorrhage with loss of consciousness of unspecified duration, initial~')insert into #T(VisitID, Diag) values('F1-B20170121081055133','R51 - Headache')insert into #T(VisitID, Diag) values('F1-B20170224123502640','S23.3XXA - Sprain of ligaments of thoracic spine, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170315131444201','S22.079A - Unspecified fracture of T9-T10 vertebra, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170219205257425','S01.111A - Laceration without foreign body of right eyelid and periocular area, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170219152441210','H53.8 - Other visual disturbances')insert into #T(VisitID, Diag) values('F0-B20170219112136071','D68.32 - Hemorrhagic disorder due to extrinsic circulating anticoagulants')insert into #T(VisitID, Diag) values('F1-B20170219134940437','R42 - Dizziness and giddiness')insert into #T(VisitID, Diag) values('F1-B20170429105539126','L02.211 - Cutaneous abscess of abdominal wall')insert into #T(VisitID, Diag) values('F0-B20170421121927410','R42 - Dizziness and giddiness')insert into #T(VisitID, Diag) values('F1-B20170102124906099','M48.56XA - Collapsed vertebra, not elsewhere classified, lumbar region, initial encounter for fractu~')insert into #T(VisitID, Diag) values('F1-B20170510111737888','I82.431 - Acute embolism and thrombosis of right popliteal vein')insert into #T(VisitID, Diag) values('F0-B20170508143521371','I82.220 - Acute embolism and thrombosis of inferior vena cava')insert into #T(VisitID, Diag) values('F0-B20170417120107339','R07.9 - Chest pain, unspecified')insert into #T(VisitID, Diag) values('F0-B20170412162804356','R07.2 - Precordial pain')insert into #T(VisitID, Diag) values('F0-B20170420104805585','I33.9 - Acute and subacute endocarditis, unspecified')insert into #T(VisitID, Diag) values('F0-B20170114054713874','S72.001A - Fracture of unspecified part of neck of right femur, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170304174140391','J95.830 - Postprocedural hemorrhage of a respiratory system organ or structure following a respirat~')insert into #T(VisitID, Diag) values('F1-B20170312065821373','R20.9 - Unspecified disturbances of skin sensation')insert into #T(VisitID, Diag) values('F0-B20170217151718169','S06.6X0A - Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170109113837694','N63 - Unspecified lump in breast')insert into #T(VisitID, Diag) values('F1-B20170319112814937','I61.8 - Other nontraumatic intracerebral hemorrhage')insert into #T(VisitID, Diag) values('F0-B20170507182837283','S37.011A - Minor contusion of right kidney, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170102014618256','J18.9 - Pneumonia, unspecified organism')insert into #T(VisitID, Diag) values('F0-B20170419185000245','I63.9 - Cerebral infarction, unspecified')insert into #T(VisitID, Diag) values('F0-B20170412191339779','K56.60 - Unspecified intestinal obstruction')insert into #T(VisitID, Diag) values('F0-B20170401102256391','E05.91 - Thyrotoxicosis, unspecified with thyrotoxic crisis or storm')insert into #T(VisitID, Diag) values('F0-B20170511212007066','T81.4XXA - Infection following a procedure, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170215150011973','G08 - Intracranial and intraspinal phlebitis and thrombophlebitis')insert into #T(VisitID, Diag) values('F1-B20170426005739534','S09.90XA - Unspecified injury of head, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170408051632260','J96.01 - Acute respiratory failure with hypoxia')insert into #T(VisitID, Diag) values('F1-B20170330213501877','R07.9 - Chest pain, unspecified')insert into #T(VisitID, Diag) values('F0-B20170326095536373','R41.82 - Altered mental status, unspecified')insert into #T(VisitID, Diag) values('F0-B20170108210042449','S30.0XXA - Contusion of lower back and pelvis, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170316155227554','K35.80 - Unspecified acute appendicitis')insert into #T(VisitID, Diag) values('F0-B20170516171032987','G89.18 - Other acute postprocedural pain')insert into #T(VisitID, Diag) values('F0-B20170217085036503','K85.90 - Acute pancreatitis without necrosis or infection, unspecified')insert into #T(VisitID, Diag) values('F0-B20170102085818264','S02.11HA - Other fracture of occiput, left side, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F1-B20170228155429538','S06.6X0A - Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170306185719729','S02.609A - Fracture of mandible, unspecified, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F1-B20170202051750769','M54.16 - Radiculopathy, lumbar region')insert into #T(VisitID, Diag) values('F1-B20170210175401375','I63.9 - Cerebral infarction, unspecified')insert into #T(VisitID, Diag) values('F0-B20170210144045184','R41.82 - Altered mental status, unspecified')insert into #T(VisitID, Diag) values('F1-B20170411202705442','J44.1 - Chronic obstructive pulmonary disease with (acute) exacerbation')insert into #T(VisitID, Diag) values('F1-B20170422180703493','O34.32 - Maternal care for cervical incompetence, second trimester')insert into #T(VisitID, Diag) values('F0-B20170321100021537','N20.0 - Calculus of kidney')insert into #T(VisitID, Diag) values('F0-B20170204220935754','I65.21 - Occlusion and stenosis of right carotid artery')insert into #T(VisitID, Diag) values('F1-B20170206001707973','F10.120 - Alcohol abuse with intoxication, uncomplicated')insert into #T(VisitID, Diag) values('F0-B20170305042947530','J44.1 - Chronic obstructive pulmonary disease with (acute) exacerbation')insert into #T(VisitID, Diag) values('F0-B20170210002801255','I63.9 - Cerebral infarction, unspecified')insert into #T(VisitID, Diag) values('F1-B20170210051539877','S22.089A - Unspecified fracture of T11-T12 vertebra, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170303233733770','S66.121A - Laceration of flexor muscle, fascia and tendon of left index finger at wrist and hand lev~')insert into #T(VisitID, Diag) values('F0-B20170307213941849','D57.00 - Hb-SS disease with crisis, unspecified')insert into #T(VisitID, Diag) values('F1-B20170308002509659','M54.9 - Dorsalgia, unspecified')insert into #T(VisitID, Diag) values('F0-B20170303233636979','R20.0 - Anesthesia of skin')insert into #T(VisitID, Diag) values('F0-B20170316221629650','R55 - Syncope and collapse')insert into #T(VisitID, Diag) values('F0-B20170331230137825','R41.82 - Altered mental status, unspecified')insert into #T(VisitID, Diag) values('F0-B20170402040551716','E10.10 - Type 1 diabetes mellitus with ketoacidosis without coma')insert into #T(VisitID, Diag) values('F0-B20170124005322406','K70.31 - Alcoholic cirrhosis of liver with ascites')insert into #T(VisitID, Diag) values('F0-B20170201110305129','S13.0XXA - Traumatic rupture of cervical intervertebral disc, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170205233829589','R56.9 - Unspecified convulsions')insert into #T(VisitID, Diag) values('F0-B20170205022821944','R07.89 - Other chest pain')insert into #T(VisitID, Diag) values('F1-B20170416005746042','I73.9 - Peripheral vascular disease, unspecified')insert into #T(VisitID, Diag) values('F1-B20170205014109829','S01.512A - Laceration without foreign body of oral cavity, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170506102903745','C22.1 - Intrahepatic bile duct carcinoma')insert into #T(VisitID, Diag) values('F0-B20170209203402749','I63.40 - Cerebral infarction due to embolism of unspecified cerebral artery')insert into #T(VisitID, Diag) values('F1-B20170518134432429','R53.81 - Other malaise')insert into #T(VisitID, Diag) values('F0-B20170104151822833','C95.00 - Acute leukemia of unspecified cell type not having achieved remission')insert into #T(VisitID, Diag) values('F0-B20170212020519038','S68.011A - Complete traumatic metacarpophalangeal amputation of right thumb, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170207165940285','G40.409 - Other generalized epilepsy and epileptic syndromes, not intractable, without status epile~')insert into #T(VisitID, Diag) values('F0-B20170310175333882','K56.60 - Unspecified intestinal obstruction')insert into #T(VisitID, Diag) values('F1-B20170321160456844','T40.4X1A - Poisoning by other synthetic narcotics, accidental (unintentional), initial encounter')insert into #T(VisitID, Diag) values('F0-B20170418093449799','I63.9 - Cerebral infarction, unspecified')insert into #T(VisitID, Diag) values('F1-B20170420213651396','S52.021A - Displaced fracture of olecranon process without intraarticular extension of right ulna, i~')insert into #T(VisitID, Diag) values('F1-B20170327214640960','R04.0 - Epistaxis')insert into #T(VisitID, Diag) values('F1-B20170412153836841','R13.10 - Dysphagia, unspecified')insert into #T(VisitID, Diag) values('F1-B20170317103056073','R91.8 - Other nonspecific abnormal finding of lung field')insert into #T(VisitID, Diag) values('F0-B20170319101433149','R10.84 - Generalized abdominal pain')insert into #T(VisitID, Diag) values('F0-B20170510130116329','S00.83XA - Contusion of other part of head, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170216113401541','K72.00 - Acute and subacute hepatic failure without coma')insert into #T(VisitID, Diag) values('F1-B20170511192011462','S32.039D - Unspecified fracture of third lumbar vertebra, subsequent encounter for fracture with rou~')insert into #T(VisitID, Diag) values('F0-B20170214141137786','R20.0 - Anesthesia of skin')insert into #T(VisitID, Diag) values('F1-B20170114060016353','S02.82XA - Fracture of other specified skull and facial bones, left side, initial encounter for clos~')insert into #T(VisitID, Diag) values('F1-B20170426141439897','C71.9 - Malignant neoplasm of brain, unspecified')insert into #T(VisitID, Diag) values('F0-B20170509093421324','Z79.01 - Long term (current) use of anticoagulants')insert into #T(VisitID, Diag) values('F0-B20170312170513238','R07.2 - Precordial pain')insert into #T(VisitID, Diag) values('F0-B20170418231850022','R16.1 - Splenomegaly, not elsewhere classified')insert into #T(VisitID, Diag) values('F1-B20170309003105249','L76.34 - Postprocedural seroma of skin and subcutaneous tissue following other procedure')insert into #T(VisitID, Diag) values('F0-B20170110035431002','I46.9 - Cardiac arrest, cause unspecified')insert into #T(VisitID, Diag) values('F1-B20170110014325773','E10.10 - Type 1 diabetes mellitus with ketoacidosis without coma')insert into #T(VisitID, Diag) values('F1-B20170321025142413','R62.7 - Adult failure to thrive')insert into #T(VisitID, Diag) values('F0-B20170115224542911','E10.10 - Type 1 diabetes mellitus with ketoacidosis without coma')insert into #T(VisitID, Diag) values('F1-B20161231215739760','S01.01XA - Laceration without foreign body of scalp, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170313153156026','M84.48XD - Pathological fracture, other site, subsequent encounter for fracture with routine healing')insert into #T(VisitID, Diag) values('F0-B20170311050057946','A41.9 - Sepsis, unspecified organism')insert into #T(VisitID, Diag) values('F1-B20170410001548150','S12.001A - Unspecified nondisplaced fracture of first cervical vertebra, initial encounter for close~')insert into #T(VisitID, Diag) values('F1-B20170210115038902','S16.1XXA - Strain of muscle, fascia and tendon at neck level, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170513055411393','D57.00 - Hb-SS disease with crisis, unspecified')insert into #T(VisitID, Diag) values('F1-B20170508120529613','S80.02XA - Contusion of left knee, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170417145008232','R41.82 - Altered mental status, unspecified')insert into #T(VisitID, Diag) values('F0-B20170220134219319','S59.221A - Salter-Harris Type II physeal fracture of lower end of radius, right arm, initial encount~')insert into #T(VisitID, Diag) values('F1-B20170217103340478','I61.8 - Other nontraumatic intracerebral hemorrhage')insert into #T(VisitID, Diag) values('F0-B20170308124023567','S01.84XA - Puncture wound with foreign body of other part of head, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170115010624654','L02.01 - Cutaneous abscess of face')insert into #T(VisitID, Diag) values('F1-B20170103175220313','N28.9 - Disorder of kidney and ureter, unspecified')insert into #T(VisitID, Diag) values('F1-B20170405081430008','I25.110 - Atherosclerotic heart disease of native coronary artery with unstable angina pectoris')insert into #T(VisitID, Diag) values('F0-B20170403154909314','I20.0 - Unstable angina')insert into #T(VisitID, Diag) values('F0-B20170403170204285','A41.9 - Sepsis, unspecified organism')insert into #T(VisitID, Diag) values('F0-B20170511140420281','S31.649A - Puncture wound with foreign body of abdominal wall, unspecified quadrant with penetration~')insert into #T(VisitID, Diag) values('F0-B20170420004148424','S71.112A - Laceration without foreign body, left thigh, initial encounter')insert into #T(VisitID, Diag) values('F1-B20170313154646123','E04.2 - Nontoxic multinodular goiter')insert into #T(VisitID, Diag) values('F1-B20170212174337810','K85.10 - Biliary acute pancreatitis without necrosis or infection')insert into #T(VisitID, Diag) values('F0-B20170303014243708','K35.2 - Acute appendicitis with generalized peritonitis')insert into #T(VisitID, Diag) values('F0-B20170220224828256','K80.50 - Calculus of bile duct without cholangitis or cholecystitis without obstruction')insert into #T(VisitID, Diag) values('F0-B20170320002704814','O14.95 - Unspecified pre-eclampsia, complicating the puerperium')insert into #T(VisitID, Diag) values('F0-B20170503223557396','S27.1XXA - Traumatic hemothorax, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170210045509974','R11.2 - Nausea with vomiting, unspecified')insert into #T(VisitID, Diag) values('F1-B20170329195946907','R07.9 - Chest pain, unspecified')insert into #T(VisitID, Diag) values('F1-B20170328000942111','F23 - Brief psychotic disorder')insert into #T(VisitID, Diag) values('F1-B20170327210710429','F10.231 - Alcohol dependence with withdrawal delirium')insert into #T(VisitID, Diag) values('F1-B20170305224321698','M54.16 - Radiculopathy, lumbar region')insert into #T(VisitID, Diag) values('F0-B20170403222214303','S06.300A - Unspecified focal traumatic brain injury without loss of consciousness, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170406205426872','G40.409 - Other generalized epilepsy and epileptic syndromes, not intractable, without status epile~')insert into #T(VisitID, Diag) values('F0-B20170406230438964','S02.31XA - Fracture of orbital floor, right side, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F1-B20170309211535490','R51 - Headache')insert into #T(VisitID, Diag) values('F1-B20170120205503236','C22.9 - Malignant neoplasm of liver, not specified as primary or secondary')insert into #T(VisitID, Diag) values('F1-B20170206124548125','K80.50 - Calculus of bile duct without cholangitis or cholecystitis without obstruction')insert into #T(VisitID, Diag) values('F0-B20170514082529368','K85.90 - Acute pancreatitis without necrosis or infection, unspecified')insert into #T(VisitID, Diag) values('F1-B20170510194348481','M54.5 - Low back pain')insert into #T(VisitID, Diag) values('F1-B20170326165402927','M54.5 - Low back pain')insert into #T(VisitID, Diag) values('F0-B20170506145543407','L51.1 - Stevens-Johnson syndrome')insert into #T(VisitID, Diag) values('F1-B20170201122201162','A41.9 - Sepsis, unspecified organism')insert into #T(VisitID, Diag) values('F0-B20170126123627557','I61.1 - Nontraumatic intracerebral hemorrhage in hemisphere, cortical')insert into #T(VisitID, Diag) values('F0-B20170203080405704','K56.60 - Unspecified intestinal obstruction')insert into #T(VisitID, Diag) values('F0-B20170113215229959','O00.90 - Unspecified ectopic pregnancy without intrauterine pregnancy')insert into #T(VisitID, Diag) values('F1-B20170322143743821','S72.141A - Displaced intertrochanteric fracture of right femur, initial encounter for closed fracture')insert into #T(VisitID, Diag) values('F0-B20170407125730445','N76.4 - Abscess of vulva')insert into #T(VisitID, Diag) values('F1-B20170329143933105','D46.9 - Myelodysplastic syndrome, unspecified')insert into #T(VisitID, Diag) values('F0-B20170115222008864','S32.020K - Wedge compression fracture of second lumbar vertebra, subsequent encounter for fracture w~')insert into #T(VisitID, Diag) values('F1-B20170514184431611','E10.9 - Type 1 diabetes mellitus without complications')insert into #T(VisitID, Diag) values('F1-B20170502210725079','R10.9 - Unspecified abdominal pain')insert into #T(VisitID, Diag) values('F1-B20170511164347456','S06.5X0A - Traumatic subdural hemorrhage without loss of consciousness, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170309092543252','I82.403 - Acute embolism and thrombosis of unspecified deep veins of lower extremity, bilateral')insert into #T(VisitID, Diag) values('F0-B20170515163304148','K35.3 - Acute appendicitis with localized peritonitis')insert into #T(VisitID, Diag) values('F0-B20170301133756577','I61.4 - Nontraumatic intracerebral hemorrhage in cerebellum')insert into #T(VisitID, Diag) values('F0-B20170226170314714','R51 - Headache')insert into #T(VisitID, Diag) values('F0-B20170503054746548','S09.90XA - Unspecified injury of head, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170125175248932','E83.52 - Hypercalcemia')insert into #T(VisitID, Diag) values('F0-B20170403193858788','R10.9 - Unspecified abdominal pain')insert into #T(VisitID, Diag) values('F0-B20170221113553369','S61.215A - Laceration without foreign body of left ring finger without damage to nail, initial encou~')insert into #T(VisitID, Diag) values('F0-B20170223164833589','F10.239 - Alcohol dependence with withdrawal, unspecified')insert into #T(VisitID, Diag) values('F0-B20170115162241071','J44.9 - Chronic obstructive pulmonary disease, unspecified')insert into #T(VisitID, Diag) values('F1-B20170422125310912','I62.9 - Nontraumatic intracranial hemorrhage, unspecified')insert into #T(VisitID, Diag) values('F1-B20170510190005040','K92.2 - Gastrointestinal hemorrhage, unspecified')insert into #T(VisitID, Diag) values('F1-B20170303153311391','R45.851 - Suicidal ideations')insert into #T(VisitID, Diag) values('F0-B20170316114353391','E85.4 - Organ-limited amyloidosis')insert into #T(VisitID, Diag) values('F0-B20170215030735080','I63.9 - Cerebral infarction, unspecified')insert into #T(VisitID, Diag) values('F1-B20170228140555073','N83.511 - Torsion of right ovary and ovarian pedicle')insert into #T(VisitID, Diag) values('F1-B20170326125531760','I63.9 - Cerebral infarction, unspecified')insert into #T(VisitID, Diag) values('F1-B20170221105351111','I62.02 - Nontraumatic subacute subdural hemorrhage')insert into #T(VisitID, Diag) values('F0-B20170513194445588','R07.9 - Chest pain, unspecified')insert into #T(VisitID, Diag) values('F0-B20170512141600724','C61 - Malignant neoplasm of prostate')insert into #T(VisitID, Diag) values('F0-B20170305003113395','S09.90XA - Unspecified injury of head, initial encounter')insert into #T(VisitID, Diag) values('F0-B20170125112945256','F23 - Brief psychotic disorder')insert into #T(VisitID, Diag) values('F0-B20170115133241223','R20.9 - Unspecified disturbances of skin sensation')insert into #T(VisitID, Diag) values('F0-B20170218125638537','R42 - Dizziness and giddiness')insert into #T(VisitID, Diag) values('F0-B20170305054557035','I63.9 - Cerebral infarction, unspecified')insert into #T(VisitID, Diag) values('F1-B20170323195954813','R10.84 - Generalized abdominal pain')insert into #T(VisitID, Diag) values('F0-B20170116140743627','I25.110 - Atherosclerotic heart disease of native coronary artery with unstable angina pectoris')
May 23, 2017 at 2:11 pm
There seems to be 8 different Diags that have a count of 4 in this set, so if you don't want ranks repeated, you would randomly leave off 2 of the ones with 4 rows. If you don't mind that then:
SELECT t.VisitID, t.Diag, g.VisitCnt, g.ranking
FROM #T t
INNER JOIN (SELECT Diag, COUNT(*) VisitCnt, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS ranking FROM #T GROUP BY Diag) g ON t.Diag = g.Diag
WHERE g.ranking <= 15
ORDER BY VisitCnt DESC;
if you want all the ones that have 4, then change the ROW_NUMBER() function to a RANK() function
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply