To get Top 10

• mandymvn

  Mr or Mrs. 500

  Points: 547

  More actions

  May 18, 2015 at 5:22 pm

  #297093

  Hi

  I have a more than 50 departments in my table and trying to get top 10, something I need to show in a report like top9 departments and then rest of them has to show as Others in 10th row. is it possible ?

  when I use top 10 from table I am getting top 10 rows but I need the top 9 list and rest as others in 10th row.

  I have table like below

  Custid, department, location

  1 Fin NY

  2 Acc NY

  3 HR MI

  4

  5

  6

  please let me know if you need more in details.

  Thanks

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  May 18, 2015 at 8:41 pm

  #1798888

  Note the article in my signature about how to best get help here. Sample data and some DDL would really help. That said...

  This wont be too hard: the solution will use a combination of DENSE_RANK() or ROW_NUMBER() to get the top 9 and FOR XML('') PATH to concatenate the rest into your 10th row. What I need to know is:

  1. TOP 10 in order of what?

  2. Can there be ties?

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Dwain Camps

  SSC Guru

  Points: 86908

  More actions

  May 18, 2015 at 10:30 pm

  #1798897

  Here's an example that illustrates what happens when you want the TOP 5 and ties are present.

  WITH SampleData (Custid, department, location, revenue) AS

  (

  SELECT 1, 'Fin','NY', 20

  UNION ALL SELECT 2,'Acc','NY', 50

  UNION ALL SELECT 3,'HR','MI', 40

  UNION ALL SELECT 4,'Acc','FL', 25

  UNION ALL SELECT 5,'HR','FL', 15

  UNION ALL SELECT 6,'Acc','MS', 28

  UNION ALL SELECT 7,'HR','MS', 40

  ),

  RankDepts AS

  (

  SELECT department, location, revenue=SUM(revenue)

  ,rnk=DENSE_RANK() OVER (ORDER BY SUM(revenue) DESC)

  FROM SampleData

  GROUP BY department, location

  )

  SELECT department = CASE WHEN rnk <= 4 THEN department ELSE 'other' END

  ,location = CASE WHEN rnk <= 4 THEN location END

  ,revenue = SUM(revenue)

  FROM RankDepts

  GROUP BY CASE WHEN rnk <= 4 THEN department ELSE 'other' END

  ,CASE WHEN rnk <= 4 THEN location END

  ORDER BY CASE WHEN CASE WHEN rnk <= 4 THEN location END IS NULL THEN 1 ELSE 0 END

  ,SUM(revenue) DESC;

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St

• mandymvn

  Mr or Mrs. 500

  Points: 547

  More actions

  May 22, 2015 at 12:24 pm

  #1800122

  Thanks!