CODEWITHSUNDEEP
sqlpostgresqlwindow-functionsrow-number
user3007361
user3007361

Reputation: 73

Rank based on sequence of dates

I am having data as below

**Heading    Date**
A          2009-02-01
B          2009-02-03
c          2009-02-05
d          2009-02-06
e          2009-02-08

I need rank as below

Heading    Date           Rank
A          2009-02-01      1
B          2009-02-03      2
c          2009-02-05      1
d          2009-02-06      2
e          2009-02-07      3

As I need rank based on date. If the date is continuous the rank should be 1, 2, 3 etc. If there is any break on dates I need to start over with 1, 2, ...

Can any one help me on this?

Upvotes: 4

Views: 6891

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658907

SELECT heading, thedate
      ,row_number() OVER (PARTITION BY grp ORDER BY thedate) AS rn
FROM  (
   SELECT *, thedate - (row_number() OVER (ORDER BY thedate))::int AS grp
   FROM   demo
   ) sub;

While you speak of "rank" you seem to want the result of the window function row_number().

  1. Form groups of consecutive days (same date in grp) in subquery sub.
  2. Number rows with another row_number() call, this time partitioned by grp.

One subquery is the bare minimum here, since window functions cannot be nested.

SQL Fiddle.

Note that I went with the second version of your contradictory sample data. And the result is as @mu suggested in his comment.
Also assuming that there are no duplicate dates. You'd have to aggregate first in this case.

Upvotes: 3

user3007361
user3007361

Reputation: 73

Bangalore BLR - Bagmane Tech Park 2013-10-11 Data Centre 0 Bangalore BLR - Bagmane Tech Park 2013-10-11 BMS 0 Bangalore BLR - Bagmane Tech Park 2013-10-12 BMS 0 Bangalore BLR - Bagmane Tech Park 2013-10-15 BMS 3

I am having data lyk this..

If last column is zero the rank should be made based on all columns..If the date is continuous like 2013-10-11 ,2013-10-12 rank should be 1,2...

If there is any break in date 2013-10-11 ,2013-10-12 and 2013-10-15 again the rank should start from 1 for 2013-10-15

Upvotes: 0

Damodaran
Damodaran

Reputation: 11065

Hi this is not correct answer, I am trying.. It is interesting..:) I am posting what I got so far: sqlfiddle

SELECT
    rank() over (order by thedate asc) as rank,
    heading, thedate
FROM 
    demo
Order by
    rank asc;

Now I am trying to get the break in dates. I don't know how? But may be these links useful

I will update if I got anything.

Edit:

I got this for mysql, I am posting this because it may helpful. Check Emulate Row_Number() Here

Given a table with two columns i and j, generate a resultset that has a derived sequential row_number column taking the values 1,2,3,... for a defined ordering of j which resets to 1 when the value of i changes

Upvotes: 0

Related Questions