Help with Ranking Functions

• a1011

  SSC Enthusiast

  Points: 180

  More actions

  June 2, 2010 at 1:29 pm

  #219663

  I need some help with ranking. I have a table similar to the "#Test" table I have posted below. What I need is ranking whenever "Event" = 1. Whenever "Event" is not 1 then the ranking resets to zero and starts from 1 again whenever the "Event" becomes 1. This explanation is a bit hazy therefore I have inserted a Table "#TestAnswer" that contains the output I need.

  The Ranking should also depend on the ID. Therefore whenever the ID changes then the Ranking should reset to 1 if the "Event"=1. The output should be ordered by ID and datecreated and should be in the order given in the "#TestAnswer" table.

  I tried to use the Row_Number, Rank and Dense_Rank functions but did not get the results I desire therefore thought that someone in the forum might be able to help me.

  if OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test

  CREATE TABLE #Test (

  ID int,

  Datecreated datetime,

  [Event] INT

  )

  INSERT INTO #Test (ID, Datecreated, [Event])

  SELECT 1, '2010-04-01 06:16:16.623', 2 UNION ALL

  SELECT 1, '2010-04-01 12:25:18.940', 0 UNION ALL

  SELECT 1, '2010-04-01 20:03:07.110', 1 UNION ALL

  SELECT 1, '2010-04-01 20:40:27.217', 1 UNION ALL

  SELECT 2, '2010-04-01 09:48:42.450', 4 UNION ALL

  SELECT 2, '2010-04-01 09:51:24.890', 1 UNION ALL

  SELECT 2, '2010-04-01 09:52:24.890', 1 UNION ALL

  SELECT 2, '2010-04-01 10:51:24.890', 4 UNION ALL

  SELECT 2, '2010-04-01 11:51:24.890', 5 UNION ALL

  SELECT 2, '2010-04-01 12:51:24.890', 1 UNION ALL

  SELECT 2, '2010-04-01 14:52:24.890', 1 UNION ALL

  SELECT 2, '2010-04-01 14:54:24.890', 1 UNION ALL

  SELECT 3, '2010-04-01 09:51:24.890', 1 UNION ALL

  SELECT 3, '2010-04-01 20:37:29.953', 1 UNION ALL

  SELECT 3, '2010-04-01 21:48:40.890', 0 UNION ALL

  SELECT 3, '2010-04-01 22:13:56.633', 1 UNION ALL

  SELECT 3, '2010-04-01 23:52:41.653', 1

  --The following is the answer that I need

  if OBJECT_ID('tempdb..#TestAnswer') IS NOT NULL DROP TABLE #TestAnswer

  CREATE TABLE #TestAnswer (

  ID int,

  Datecreated datetime,

  [Event] INT,

  Rank int

  )

  INSERT INTO #Test (ID, Datecreated, [Event], Rank)

  SELECT 1, '2010-04-01 06:16:16.623', 2, Null UNION ALL

  SELECT 1, '2010-04-01 12:25:18.940', 0, Null UNION ALL

  SELECT 1, '2010-04-01 20:03:07.110', 1, 1 UNION ALL

  SELECT 1, '2010-04-01 20:40:27.217', 1, 2 UNION ALL

  SELECT 2, '2010-04-01 09:48:42.450', 4, Null UNION ALL

  SELECT 2, '2010-04-01 09:51:24.890', 1, 1 UNION ALL

  SELECT 2, '2010-04-01 09:52:24.890', 1, 2 UNION ALL

  SELECT 2, '2010-04-01 10:51:24.890', 4, Null UNION ALL

  SELECT 2, '2010-04-01 11:51:24.890', 5, Null UNION ALL

  SELECT 2, '2010-04-01 12:51:24.890', 1, 1 UNION ALL

  SELECT 2, '2010-04-01 14:52:24.890', 1, 2 UNION ALL

  SELECT 2, '2010-04-01 14:54:24.890', 1, 3 UNION ALL

  SELECT 3, '2010-04-01 09:51:24.890', 1, 1 UNION ALL

  SELECT 3, '2010-04-01 20:37:29.953', 1, 2 UNION ALL

  SELECT 3, '2010-04-01 21:48:40.890', 0, Null UNION ALL

  SELECT 3, '2010-04-01 22:13:56.633', 1, 1 UNION ALL

  SELECT 3, '2010-04-01 23:52:41.653', 1, 2

• a1011

  SSC Enthusiast

  Points: 180

  More actions

  June 2, 2010 at 2:23 pm

  #1175571

  I just got the solution to the above problem I had posted. If any of you have a better solution then please post it. The solution is:

  SELECT

  ID,

  Datecreated,

  EVENT,

  CASEWHEN EVENT = 1

  THEN RANK()OVER(PARTITION BY ID, RANK1 ORDER BY DATECREATED)

  ELSE NULL END AS RANK1

  FROM

  (

  SELECT

  *,

  RANK()OVER (PARTITION BY ID ORDER BY DATECREATED)

  - CASEWHEN EVENT = 1

  THEN RANK()OVER(PARTITION BY ID, EVENT ORDER BY DATECREATED)

  ELSE NULL END AS RANK1

  FROM

  #Test

  ) AS A

  ORDER BY

  ID,

  DATECREATED

• divyanth

  SSCarpal Tunnel

  Points: 4303

  More actions

  June 2, 2010 at 2:34 pm

  #1175576

  using a CTE instead of a sub query would improve the performance of the query

  WITH CTE AS

  (

  SELECT

  *,

  RANK()OVER (PARTITION BY ID ORDER BY DATECREATED)

  - CASE WHEN EVENT = 1

  THEN RANK()OVER(PARTITION BY ID, EVENT ORDER BY DATECREATED)

  ELSE NULL END AS RANK1

  FROM

  #Test

  )

  SELECT

  ID,

  Datecreated,

  EVENT,

  CASE WHEN EVENT = 1

  THEN RANK()OVER(PARTITION BY ID, RANK1 ORDER BY DATECREATED)

  ELSE NULL END AS RANK1

  FROM CTE C

  ORDER BY ID, DATECREATED

• a1011

  SSC Enthusiast

  Points: 180

  More actions

  June 2, 2010 at 2:42 pm

  #1175579

  [highlight]A Small correction in the Create statement the Event field should be an INTEGER not a BIT because bit can store only 1's and 0's[/highlight]

  Thank you for pointing that out. I have corrected the Create Table statements.

• WayneS

  SSC Guru

  Points: 95461

  More actions

  June 2, 2010 at 2:43 pm

  #1175581

  divyanth (6/2/2010)

  ---

  using a CTE instead of a sub query would improve the performance of the query

  Nope, not at all. A CTE is essentially a pre-defined sub-query (unless it's using recursion, which this isn't), and using one does NOTHING by itself to improve performance. (It does, IMO, improve read-ability of the code.) Based upon the two queries, they should return an IDENTICAL execution plan.

  Have you something that proves your claim?

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• LutzM

  SSC Guru

  Points: 107049

  More actions

  June 2, 2010 at 2:52 pm

  #1175585

  divyanth (6/2/2010)

  ---

  using a CTE instead of a sub query would improve the performance of the query

  ...

  AFAIK this is actually not true, since both, CTE and subquery will result in exactly the same exacution plan. It's just easier to read (for some/most people)...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• divyanth

  SSCarpal Tunnel

  Points: 4303

  More actions

  June 3, 2010 at 7:47 am

  #1175953

  for some reason, I was under a wrong impression that its much faster compared to Sub Query.. Thanks a lot for Correcting Me Guyz 🙂