Get Max Row Only if no Ties

• OTF

  SSCertifiable

  Points: 6104

  More actions

  May 10, 2011 at 8:44 am

  #239337

  Hi

  Sample Data:

  CREATE TABLE #test (key_value INT, test_value INT);

  INSERT #test

  SELECT 1, 2

  UNION ALL

  SELECT 1, 3

  UNION ALL

  SELECT 3, 4

  UNION ALL

  SELECT 3, 4

  UNION ALL

  SELECT 3, 4

  I would like to return the max test_value for each key_value only if it does not have a tie.

  So, the results I would expect from the above would be:

  key_value test_value

  13

  Thanks for your help.

• OTF

  SSCertifiable

  Points: 6104

  More actions

  May 10, 2011 at 9:00 am

  #1322379

  Sorry, I should point out that my expected results are actually:

  key_value=1

  test_value=3

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  May 10, 2011 at 9:10 am

  #1322388

  That gives you the result you want but I'm not sure about the way I'm getting there. Test it on your full dataset to confirm plz.

  SET IMPLICIT_TRANSACTIONS ON

  CREATE TABLE #test (key_value INT, test_value INT);

  INSERT #test

  SELECT 1, 2

  UNION ALL

  SELECT 1, 3

  UNION ALL

  SELECT 3, 4

  UNION ALL

  SELECT 3, 4

  UNION ALL

  SELECT 3, 4

  SELECT TOP 1

  key_value

  , test_value

  FROM

  #test

  GROUP BY

  key_value

  , test_value

  HAVING

  COUNT(*) = 1

  ORDER BY

  test_value DESC

  DROP TABLE #test

  ROLLBACK

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  May 10, 2011 at 10:28 am

  #1322473

  Ninja's_RGR'us (5/10/2011)

  ---

  That gives you the result you want but I'm not sure about the way I'm getting there. Test it on your full dataset to confirm plz.

  But that will not satisfy 'the max test_value for each key_value only'

  This should

  WITH cte (key_value, test_value)

  AS (SELECT key_value, test_value

  FROM #test

  GROUP BY key_value, test_value

  HAVING COUNT(*) = 1)

  SELECT key_value, MAX(test_value) AS [test_value]

  FROM cte

  GROUP BY key_value

  Far away is close at hand in the images of elsewhere.
  Anon.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  May 10, 2011 at 10:30 am

  #1322476

  I knew I had missed something ovbious...

  Tx for the assist.

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  May 10, 2011 at 10:34 am

  #1322481

  Ninja's_RGR'us (5/10/2011)

  ---

  I knew I had missed something ovbious...

  Tx for the assist.

  No worries, I had to read the post a few times to spot it myself 🙂

  My only concern is what

  Sorry, I should point out that my expected results are actually:

  key_value=1

  test_value=3

  actually means

  Far away is close at hand in the images of elsewhere.
  Anon.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  May 10, 2011 at 10:40 am

  #1322486

  David Burrows (5/10/2011)

  ---

  Ninja's_RGR'us (5/10/2011)

  ---

  I knew I had missed something ovbious...

  Tx for the assist.

  No worries, I had to read the post a few times to spot it myself 🙂

  My only concern is what

  Sorry, I should point out that my expected results are actually:

  key_value=1

  test_value=3

  actually means

  That's why I asked to test on more data. I was right strickly looking at those numbers but I couldn't be sure.