#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    5
    Rep Power
    0

    How to select that particular row from over function


    How to write sql to select following record?
    date1 date2 value
    1/2001 2/2001 2
    1/2001 3/2001 3
    1/2001 4/2001 0 select first zero for 1/2001
    1/2001 5/2001 0
    2/2001 3/2001 1
    2/2001 4/2001 0 select first zero for 2/2001
    2/2001 5/2001 0
    2/2001 6/2001 0
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    Code:
    select date1, date2, value
    from (
       select date1, 
                date2, 
                value,
                row_number() over (partition by date1 order by date2 asc) as rn
       from the_unknown_table
    ) t
    where rn = 1 
    and value = 0
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    5
    Rep Power
    0
    Originally Posted by shammat
    Code:
    select date1, date2, value
    from (
       select date1, 
                date2, 
                value,
                row_number() over (partition by date1 order by date2 asc) as rn
       from the_unknown_table
    ) t
    where rn = 1 
    and value = 0
    R u sure this wil work? why rn need to be 1? it is not
    How the row_number() calculated? start from each new date1?

    Actually the logic should be if 0 not there i have to return row number 5's data in sequence of data1-data2.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    5
    Rep Power
    0
    where rn = 1 is not working because rn is not filter by value = 0 first. Both at run together. So at the end only row 1 and value=0 is return.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    6
    Rep Power
    0
    Recommendation: store your dates like "2001/01". The dates will eventually sort incorrectly if you store them like "1/2001". Consider how "2/2001" compares to "10/2001", or to "1/2002".

IMN logo majestic logo threadwatch logo seochat tools logo