Select record with multiple rows in each rank after dense_rank

• jutiyi

  SSC Rookie

  Points: 49

  More actions

  December 5, 2010 at 11:45 pm

  #242159

  I have write a query make use of dense_rank() to return following table

  Col1 Col2 Col3 [Dense_Rank]

  .. .. .. 1

  .. .. .. 2

  .. .. .. 2

  .. .. .. 1

  After that, I only want to retrieve record that have multiple rows for the same rank, which is

  Col1 Col2 Col3 [Dense_Rank]

  .. .. .. 2

  .. .. .. 2

  How should I write query, I tried use group by and having but it still cannot work cos I only want to group [Dense_Rank]..

  Anyone know?Thanks.

• VSSGeorge

  SSCrazy Eights

  Points: 8151

  More actions

  December 6, 2010 at 3:06 am

  #1258932

  Hi

  Pls move this question to SQL Server 2005 section...

  You will get a better response.

  In SQL Server 2000, Dense_Rank is not supported.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 6, 2010 at 4:36 am

  #1258946

  SELECT Col1, Col2, Col3, Dense_Rank_Column, COUNT(*)

  FROM (

  Col1 Col2 Col3 Dense_Rank_Column = [Dense_Rank]

  .. .. .. 1

  .. .. .. 2

  .. .. .. 2

  .. .. .. 1

  ) d

  GROUP BY Dense_Rank_Column

  HAVING COUNT(*) > 1

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• angelnjj

  SSC-Addicted

  Points: 451

  More actions

  February 14, 2011 at 9:41 am

  #1286572

  Did anyone ever get a solution to this? I have the same problem, when I use the suggested query...I get msg re: columns being invalid in the select list b/c they aren't in an aggregate or GROUP BY clause. I can't group by those other columns b/c they aren't distinct - so grouping give me a cont(*) of 1 for each.

  USED:

  SELECT denserank, *

  FROM

  (SELECT dense_rank() over (order by col3, col2, col4) as denserank, * FROM table1) d

  GROUP BY denserank

  HAVING COUNT(*) > 1

  WHEN ADD GROUPING COLUMNS, RETURNS NO RESULTS:

  SELECT col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, denserank

  FROM

  (SELECT dense_rank() over (order by col3, col2, col4) as denserank, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11

  FROM table1) d

  GROUP BY denserank, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11

  HAVING COUNT(*) > 1