3

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user3007361
  • 63
  • 1
  • 2
  • 7

3 Answers3

3
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.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

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

Community
  • 1
  • 1
Damodaran
  • 10,882
  • 10
  • 60
  • 81
  • 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 – user3007361 Nov 19 '13 at 07:51
0

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

user3007361
  • 63
  • 1
  • 2
  • 7
  • This should probably be another question (or an edit to this one), not an answer. But before you post the question add Postgres version, table definitions and explain what `should be made based on all columns` is supposed to mean. – Erwin Brandstetter Nov 19 '13 at 13:43