Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Unanswered: Help with a query

    I am trying to sort and group rows with another column to show the order. Eg:

    JOB DATE
    J1 09/11/09 13:00
    J1 09/11/09 12:00
    J2 08/11/09 09:00
    J2 09/11/09 10:00
    J2 10/11/09 11:00

    I want to select and get back

    JOB DATE NUMBER
    J1 09/11/09 12:00 1
    J1 09/11/09 13:00 2
    J2 08/11/09 09:00 1
    J2 08/11/09 10:00 2
    J2 10/11/09 11:00 3

    I can sort the records but I do no know how to add the NUMBER column with a set of ordered numbers for each JOB. Thank you very much for any assistance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sql server 4.5? sql server 2000? sql server 2005?

    the solution will depend on what version you're running
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2009
    Posts
    6
    Hi, I am using SQL2008 and I am trying to execute the query from VB.NET at present. Thank you.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT job
         , [date]
         , ROW_NUMBER() OVER (PARTITION BY job ORDER BY [date] ASC) AS number
      FROM daTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2009
    Posts
    6
    Thank you very much indeed -that is just what I needed. Excellent. Many many thanks.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Really?

    I need my MT....uh, SQL 2k8
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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