Most Occuring value in a column

• sandeepchirayil

  SSC Rookie

  Points: 40

  More actions

  February 18, 2012 at 4:12 am

  #255928

  Hi Friends,

  I've an emergency requirment from my client to figure out a solution for finding the most occuring value in a column. An example may help you guys to figure our what I exactly needs

  This is my source table

  name1

  name2

  name3

  name1

  name4

  name2

  name1

  name5

  name6

  name1

  name2

  name1

  name7

  This is the result of a column from an SQL select query. In this column the most number of occuring value is name1 obviously. So I need to find a query to find out this name1 from the column.

  Thanks in advance

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  February 18, 2012 at 5:01 am

  #1448886

  This?

  DECLARE @Table TABLE

  ( ColVal VARCHAR(10) )

  INSERT INTO @Table (ColVal)

  SELECT 'name1'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name3'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name4'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name5'

  UNION ALL SELECT 'name6'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name7'

  ; WITH CTE ( ColVal, [Count]) AS

  (

  SELECT Colval , COUNT(*) [Count]

  FROM @Table

  GROUP BY Colval

  )

  SELECT TOP 1 ColVal

  FROM CTE

  ORDER BY [Count] DESC

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  February 18, 2012 at 5:05 am

  #1448887

  Mind you, the above query works only if u have single value out of the table ; if u have multiple-qualifying rows, then we must use another method!

  {Edit - Gosh, embarrasing typos in the original post :(}

• sandeepchirayil

  SSC Rookie

  Points: 40

  More actions

  February 18, 2012 at 7:09 am

  #1448899

  Thanks Bro.... My requirement is almost similar to this... But I've got an idea from your query. Thanks a lot.. Cheers..

• Jeff Moden

  SSC Guru

  Points: 1004664

  More actions

  February 18, 2012 at 10:33 am

  #1448916

  ColdCoffee (2/18/2012)

  ---

  This?

  DECLARE @Table TABLE

  ( ColVal VARCHAR(10) )

  INSERT INTO @Table (ColVal)

  SELECT 'name1'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name3'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name4'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name5'

  UNION ALL SELECT 'name6'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name7'

  ; WITH CTE ( ColVal, [Count]) AS

  (

  SELECT Colval , COUNT(*) [Count]

  FROM @Table

  GROUP BY Colval

  )

  SELECT TOP 1 ColVal

  FROM CTE

  ORDER BY [Count] DESC

  "Use the Force, Luke!"

  Although the execution plans are identical and the performance is likely identical, as well, sometimes the "old ways" lead to simpler code. Don't forget the "old ways" CC. 😉

  SELECT TOP 1

  Colval

  FROM @Table

  GROUP BY Colval

  ORDER BY COUNT(*) DESC

  ;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  February 18, 2012 at 12:06 pm

  #1448926

  Jeff Moden (2/18/2012)

  ---

  "Use the Force, Luke!"

  Although the execution plans are identical and the performance is likely identical, as well, sometimes the "old ways" lead to simpler code. Don't forget the "old ways" CC. 😉

  SELECT TOP 1

  Colval

  FROM @Table

  GROUP BY Colval

  ORDER BY COUNT(*) DESC

  ;

  Jeff, know what, i put the code at around 4 AM in the morning, should be due to a brain fart 🙁

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  February 18, 2012 at 4:42 pm

  #1448944

  Going through the example again adding extra data, ( new sample has Name1 and name2 appearing 5 times each), here is the code

  DECLARE @Table TABLE

  ( ColVal VARCHAR(10) )

  INSERT INTO @Table (ColVal)

  SELECT 'name1'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name3'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name4'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name5'

  UNION ALL SELECT 'name6'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name7'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name2';

  ; WITH Base AS

  (

  SELECT ColVal

  , [Count] = COUNT(*)

  FROM @Table

  GROUP BY Colval

  )

  , RankedData AS

  (

  SELECT ColVal , [Count]

  ,Rnk = RANK() OVER(ORDER BY [Count] DESC)

  FROM Base

  )

  SELECTColVal

  FROM RankedData

  WHERE Rnk =1

• Jeff Moden

  SSC Guru

  Points: 1004664

  More actions

  February 18, 2012 at 6:31 pm

  #1448953

  ColdCoffee (2/18/2012)

  ---

  Jeff Moden (2/18/2012)

  ---

  "Use the Force, Luke!"

  Although the execution plans are identical and the performance is likely identical, as well, sometimes the "old ways" lead to simpler code. Don't forget the "old ways" CC. 😉

  SELECT TOP 1

  Colval

  FROM @Table

  GROUP BY Colval

  ORDER BY COUNT(*) DESC

  ;

  Jeff, know what, i put the code at around 4 AM in the morning, should be due to a brain fart 🙁

  BWAA-HAAA!!! That's when I have my best brain farts! 😀

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004664

  More actions

  February 18, 2012 at 6:35 pm

  #1448955

  ColdCoffee (2/18/2012)

  ---

  Going through the example again adding extra data, ( new sample has Name1 and name2 appearing 5 times each), here is the code

  DECLARE @Table TABLE

  ( ColVal VARCHAR(10) )

  INSERT INTO @Table (ColVal)

  SELECT 'name1'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name3'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name4'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name5'

  UNION ALL SELECT 'name6'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name7'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name2';

  ; WITH Base AS

  (

  SELECT ColVal

  , [Count] = COUNT(*)

  FROM @Table

  GROUP BY Colval

  )

  , RankedData AS

  (

  SELECT ColVal , [Count]

  ,Rnk = RANK() OVER(ORDER BY [Count] DESC)

  FROM Base

  )

  SELECTColVal

  FROM RankedData

  WHERE Rnk =1

  I say again, don't forget the "old ways". They make life simpler. 😀

  SELECT TOP 1 WITH TIES

  Colval

  FROM @Table

  GROUP BY Colval

  ORDER BY COUNT(*) DESC

  ;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  February 18, 2012 at 6:43 pm

  #1448957

  Jeff Moden (2/18/2012)

  ---

  ColdCoffee (2/18/2012)

  ---

  Going through the example again adding extra data, ( new sample has Name1 and name2 appearing 5 times each), here is the code

  DECLARE @Table TABLE

  ( ColVal VARCHAR(10) )

  INSERT INTO @Table (ColVal)

  SELECT 'name1'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name3'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name4'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name5'

  UNION ALL SELECT 'name6'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name1'

  UNION ALL SELECT 'name7'

  UNION ALL SELECT 'name2'

  UNION ALL SELECT 'name2';

  ; WITH Base AS

  (

  SELECT ColVal

  , [Count] = COUNT(*)

  FROM @Table

  GROUP BY Colval

  )

  , RankedData AS

  (

  SELECT ColVal , [Count]

  ,Rnk = RANK() OVER(ORDER BY [Count] DESC)

  FROM Base

  )

  SELECTColVal

  FROM RankedData

  WHERE Rnk =1

  I say again, don't forget the "old ways". They make life simpler. 😀

  SELECT TOP 1 WITH TIES

  Colval

  FROM @Table

  GROUP BY Colval

  ORDER BY COUNT(*) DESC

  ;

  Ate a lot of beans, so my brain fart continues! And know what, pls don laugh, i was thinking on using ur splitter functions and some alien methods to find the rows with ties :hehe:

• Jeff Moden

  SSC Guru

  Points: 1004664

  More actions

  February 18, 2012 at 7:03 pm

  #1448958

  Heh. Not to worry. I've eaten my own share of bad beans. 🙂

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)