Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Row_Number() or RANK() Function

    I am trying to have my query build up a Row_Number() or RANK() function so that I can show a walk through for different booking dates. This SQL below is what I'm using but when running it the Row_Numbers are all the same, they show as 1, and I want it to change when it gets to a new prop_seql number. where have I gone wrong?
    Code:
    SELECT *
    FROM
    (
    		SELECT ROW_NUMBER() OVER(PARTITION BY prop_seql, booking_date ORDER By db_serv_job_no DESC) AS
    'row_number', prop_seql, db_serv_job_no, booking_date
    		FROM servicing_jobs_audit
    WHERE     (NOT (prop_seql = 1))
    )dtOrg
    WHERE row_number = 1
    or
    Code:
    SELECT *
    FROM
    (
    		SELECT RANK() OVER(PARTITION BY prop_seql, booking_date ORDER By db_serv_job_no DESC) AS
    'row_number', prop_seql, db_serv_job_no, booking_date
    		FROM servicing_jobs_audit
    WHERE     (NOT (prop_seql = 1))
    )dtOrg
    WHERE row_number = 1

  2. #2
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    If I delete the line WHERE row_number = 1 then it will do what I'm looking for but there will be many different 1,2,3 and so on within the same prop_seql

    I dont understand why

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you supply some sample data and the results you want? Once we see that is should be simple - I'm just not clear exactly what you want. I'm pretty certain you want to remove
    booking_date from the partition clause to start though.

  4. #4
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Attached are 2 text files, I've ignored the RANK() as I dont think thats what am trying to do or is it?

    Text File RowNumber relates to the SQL ROW_NUMBER() in the original tread.
    RowNumberWithout1 is the same SQL but with the line WHERE row_number = 1 deleted.

    What I am trying to do is by Prop_seql put in order the dates as they were done in the database, 1,2,3 and so on

    for example

    row_number,prop_seql,db_serv_job_no,booking_date
    1,2,50485,1/11/2007 13:02:00
    1,3,21332,22/3/2007 14:12:00
    2,3,21332,4/5/2007 14:12:00
    3,3,57413,13/2/2008 12:36:00
    4,3,57413,3/3/2008 00:00:00
    5,3,57413,7/3/2008 12:36:00

    this seems to work on the RowNumberWithout1 Text File but for some reason it sometimes starts from 1 again on the same prop_seql, dont understand why.
    Attached Files Attached Files

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - just drop booking_date from the partition clause.

  6. #6
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Excellent, thats it Thanks!!

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just to follow up so you get it for the future - do you understand why?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •