Geek Answers Handbook

Date Sequence Rank

I have the data 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 a rank lower

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

I need a date based rank. If the date is continuous, the rank should be 1, 2, 3, etc. If there are any breaks in dates, I need to start with 1, 2, ...

Can someone help me with this?

+2
source share
3 answers
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 are talking about "rank", you seem to need the result of the window function row_number().

  • Groups of consecutive day forms (same date in grp) in the subquery sub.
  • The number of rows with another call row_number(), this time divided by grp.

- , .

SQL Fiddle.

, . @mu. , . .

+2

, , .. ..:) , : sqlfiddle

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

. , ?

, -.

Edit:

mysql, , . Emulate Row_Number()

j, , row_number, 1,2,3,... j, 1,

0

BLR - 2013-10-11 0 BLR - 2013-10-11 BMS 0 BLR - 2013-10-12 BMS 0 BLR - 2013-10-15 BMS 3

lyk this..

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

If there is a break in 2013-2013, 2013-10-12 and 2013-10-15 again, the rating should start from 1 on 2013-10-15

0
source

More articles:


All Articles