ORACLE KEEP Function

• iainmulcahy

  Valued Member

  Points: 56

  More actions

  December 4, 2008 at 2:54 am

  #194710

  Hi there,

  Is there any equivalent function for this in SQL 2005 SQL? I need to DENSE_RANK() a column based on the first (max)value of each TOP N paritioned group, so that I can sort the group result set by this value.

  In Oracle I use the following

  SELECT

  SELECT Group_id,

  DName,

  MaxReads,

  TopN,

  MAX (MaxReads) KEEP (dense_rank first ORDER BY topn) over (PARTITION BY Group_ID) maxRank

  FROM (TOP N Query)

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  December 4, 2008 at 9:22 am

  #907707

  There is not a KEEP function in SQL Server. You would need to do something like this:

  create table #t (n int)

  insert into #t (n) values (100)

  insert into #t (n) values (200)

  insert into #t (n) values (300)

  insert into #t (n) values (300)

  select n

  from

  (select n,

  dense_rank() over(

  order by cnt desc) as

  rnk

  from

  (select n,

  count(*) as

  cnt

  from #t

  group by n)

  x)

  y

  where rnk = 1

  Which I got from here[/url].

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question