List first one in each group

• CAGreensfelder

  SSC Eights!

  Points: 919

  More actions

  February 16, 2012 at 2:58 pm

  #256877

  I'm having a brain dead day. I used to know how to do this.

  I have several thousand providers in a table. I need to find one record for each distinct value of the specialty.

  Here's the data:

  CREATE TABLE [dbo].TestProviders(

  [ProviderID] [int] IDENTITY(1,1) NOT NULL,

  [Specialty] [nvarchar](50) NULL

  ) ON [PRIMARY]

  INSERT TestProviders (Specialty) VALUES ('Pediatrics')

  INSERT TestProviders (Specialty) VALUES ('Pediatrics')

  INSERT TestProviders (Specialty) VALUES ('Pediatrics')

  INSERT TestProviders (Specialty) VALUES ('Pediatrics')

  INSERT TestProviders (Specialty) VALUES ('Pediatrics')

  INSERT TestProviders (Specialty) VALUES ('Internal Medicine')

  INSERT TestProviders (Specialty) VALUES ('Internal Medicine')

  INSERT TestProviders (Specialty) VALUES ('Internal Medicine')

  INSERT TestProviders (Specialty) VALUES ('Internal Medicine')

  INSERT TestProviders (Specialty) VALUES ('Radiology')

  INSERT TestProviders (Specialty) VALUES ('Radiology')

  INSERT TestProviders (Specialty) VALUES ('Radiology')

  INSERT TestProviders (Specialty) VALUES ('Oncology')

  INSERT TestProviders (Specialty) VALUES ('Oncology')

  I'm looking for the ID of one record with Specialty='Pediatrics', one for 'Internal Medicine'... and it doesn't really matter which one. (I'm mining for test data.)

  Expected output:

  1 Pediatrics

  6 Internal Medicine

  10 radiology

  13 Oncology

• roryp 96873

  SSCertifiable

  Points: 6649

  More actions

  February 16, 2012 at 3:08 pm

  #1448188

  select specialty, MIN(providerid)

  from TestProviders

  group by specialty

• CAGreensfelder

  SSC Eights!

  Points: 919

  More actions

  February 16, 2012 at 7:35 pm

  #1448253

  Doh! Told you I was brain dead! 😉