Select One of Three Values that are Not NULL

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  May 6, 2014 at 10:30 am

  #292962

  Hello Everyone

  I hope that you are having a very nice day.

  I am working on some data that is JOINing to another table. Not a big thing. In the child table, there are different values for a single ID. I want to be able to select the Max ColorID that is Not Null, for each distinct CarID. The CarID is what I am joining the other table with. I need assistance in selecting the distinct row with the Max ColorID that is not Null. All this data is made up, so nothing looks logical in the design.

  DECLARE @ColorList TABLE

  (

  CarID float

  , ColorID int

  )

  INSERT INTO @ColorList

  SELECT 1.55948815793043E+15, 9 UNION ALL

  SELECT 1.55948815793043E+15, 27 UNION ALL

  SELECT 1.62851796905743E+15, NULL UNION ALL

  SELECT 1.62851796905743E+15, 27 UNION ALL

  SELECT 1.62851796905743E+15, 3 UNION ALL

  SELECT 1.51964586107807E+15, 1 UNION ALL

  SELECT 1.51964586107807E+15, 9 UNION ALL

  SELECT 1.55948815793043E+15, 9 UNION ALL

  SELECT 1.55948815793043E+15, 27 UNION ALL

  SELECT 1.47514023011517E+15, 5 UNION ALL

  SELECT 1.47514023011517E+15, NULL UNION ALL

  SELECT 1.64967408641916E+15, 27 UNION ALL

  SELECT 1.51964586107807E+15, 9 UNION ALL

  SELECT 1.51964586107807E+15, 1 UNION ALL

  SELECT 1.56103326128036E+15, 27 UNION ALL

  SELECT 1.56103326128036E+15, 6 UNION ALL

  SELECT 1.49856249351719E+15, NULL UNION ALL

  SELECT 1.49856249351719E+15, 9 UNION ALL

  SELECT 1.5736407022847E+15, 6 UNION ALL

  SELECT 1.64664602022073E+15, 27 UNION ALL

  SELECT 1.54762707538673E+15, 9 UNION ALL

  SELECT 1.51964244007807E+15, NULL UNION ALL

  SELECT 1.51964244007807E+15, 1 UNION ALL

  SELECT 1.51964244007807E+15, 27

  SELECT * FROM @ColorList

  So this would be the resultset:

  1.55948815793043E+15, 27

  1.62851796905743E+15, 27

  1.51964586107807E+15, 9

  1.55948815793043E+15, 27

  1.47514023011517E+15, 5

  1.64967408641916E+15, 27

  1.51964586107807E+15, 9

  1.56103326128036E+15, 27

  1.49856249351719E+15, 9

  1.5736407022847E+15, 6

  1.64664602022073E+15, 27

  1.51964244007807E+15, 27

  I have tried some things, but I am not very good at this kind of stuff.

  I greatly appreciate all your assistance, comments, suggestions and samples

  Thank you in advance for your time

  Andrew SQLDBA

• Budd

  Hall of Fame

  Points: 3772

  More actions

  May 6, 2014 at 10:47 am

  #1711183

  Try this

  SELECT CarID, MAX(ColorID) FROM @ColorList GROUP BY CarID

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  May 6, 2014 at 10:51 am

  #1711184

  You can use the Window functions

  😎

  DECLARE @ColorList TABLE

  (

  CarID float

  , ColorID int

  )

  INSERT INTO @ColorList

  SELECT 1.55948815793043E+15, 9 UNION ALL

  SELECT 1.55948815793043E+15, 27 UNION ALL

  SELECT 1.62851796905743E+15, NULL UNION ALL

  SELECT 1.62851796905743E+15, 27 UNION ALL

  SELECT 1.62851796905743E+15, 3 UNION ALL

  SELECT 1.51964586107807E+15, 1 UNION ALL

  SELECT 1.51964586107807E+15, 9 UNION ALL

  SELECT 1.55948815793043E+15, 9 UNION ALL

  SELECT 1.55948815793043E+15, 27 UNION ALL

  SELECT 1.47514023011517E+15, 5 UNION ALL

  SELECT 1.47514023011517E+15, NULL UNION ALL

  SELECT 1.64967408641916E+15, 27 UNION ALL

  SELECT 1.51964586107807E+15, 9 UNION ALL

  SELECT 1.51964586107807E+15, 1 UNION ALL

  SELECT 1.56103326128036E+15, 27 UNION ALL

  SELECT 1.56103326128036E+15, 6 UNION ALL

  SELECT 1.49856249351719E+15, NULL UNION ALL

  SELECT 1.49856249351719E+15, 9 UNION ALL

  SELECT 1.5736407022847E+15, 6 UNION ALL

  SELECT 1.64664602022073E+15, 27 UNION ALL

  SELECT 1.54762707538673E+15, 9 UNION ALL

  SELECT 1.51964244007807E+15, NULL UNION ALL

  SELECT 1.51964244007807E+15, 1 UNION ALL

  SELECT 1.51964244007807E+15, 27

  SELECT

  *

  FROM

  (

  SELECT

  ROW_NUMBER() OVER (PARTITION BY CL.CarID ORDER BY (SELECT NULL)) AS CARD_RID

  ,CL.CarID

  ,MAX(CL.ColorID) OVER (PARTITION BY CL.CarID) AS ColorID

  FROM @ColorList CL

  ) AS X WHERE X.CARD_RID = 1;

  Results

  CarID ColorID

  ---------------------- -----------

  1.47514023011517E+15 5

  1.49856249351719E+15 9

  1.51964244007807E+15 27

  1.51964586107807E+15 9

  1.54762707538673E+15 9

  1.55948815793043E+15 27

  1.56103326128036E+15 27

  1.5736407022847E+15 6

  1.62851796905743E+15 27

  1.64664602022073E+15 27

  1.64967408641916E+15 27

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  May 6, 2014 at 10:52 am

  #1711185

  AndrewSQLDBA (5/6/2014)

  ---

  Hello Everyone

  I hope that you are having a very nice day.

  BTW: Yes a jolly good day! 😀