Post reply

Partition and order the records using sql

  • October 8, 2014 at 4:44 am

    #299867

    Hi All,

    I have the below requirement: (Please provide the sql script for this)

    1. Group records according to docno column.

    2. Records will sort in desc order. (According to date1 column)

    3. In date1 column if more than one date is same than we ll consider the date2 column.

    EX: 2008-04-30 00:00:00is same here so sorting will happen based on Date2 column. So internal sorting should happen instead assigning random values.

    4. Number column is the expected output column.

    docnodate1 date2 Number

    d1 2008-08-25 00:00:00 2009-09-08 11:23:41 1

    d1 2008-04-30 00:00:00 2008-09-08 14:40:53 2

    d1 2008-04-30 00:00:00 2008-09-08 14:29:43 3

    d1 2008-04-30 00:00:00 2008-09-08 13:30:04 4

    d1 2008-04-30 00:00:00 2008-09-08 12:46:28 5

    d1 2008-04-30 00:00:00 2008-09-08 11:19:31 6

    d2 2007-11-13 00:00:00 2009-09-08 11:24:19 1

    d2 2007-11-13 00:00:00 2009-09-08 11:24:14 2

    d2 2007-11-13 00:00:00 2009-09-08 11:23:41 3

    d2 2007-11-13 00:00:00 2009-09-08 11:23:11 4

    d2 2007-11-13 00:00:00 2009-09-08 11:21:59 5

    d2 2007-10-05 00:00:00 2009-09-08 11:23:30 6

    d2 2004-09-03 00:00:00 2008-09-08 13:10:27 7

    Thanks

    Rohit

  • October 8, 2014 at 5:19 am

    #1750890

    Homework? What have you tried? Hint: use GROUP BY and ORDER BY.

    John

  • October 8, 2014 at 5:22 am

    #1750893

    John Mitchell-245523 (10/8/2014)

    Homework? What have you tried? Hint: use GROUP BY and ORDER BY.

    John

    +1. Even phrased like a homework assignment.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • October 8, 2014 at 9:33 am

    #1751029

    Hi John,

    I faced such issue in my work.

    I used two row_number() function

    1st row_number() with partition by docno and date1

    2nd row_number() with partition by docno.

    But i could not order them in proper sequence. Is there any other way we can order such values.

    Thanks

    Rohit

  • October 8, 2014 at 9:38 am

    #1751031

    Rohit

    You didn't take my hint, then? Please will you post table DDL in the form of CREATE TABLE statement(s), sample data in the form of INSERT statements, expected results based on the sample data, and the query you have tried.

    Thanks

    John

  • October 8, 2014 at 9:46 am

    #1751036

    rohit.kumar.barik (10/8/2014)

    Hi John,

    I faced such issue in my work.

    I used two row_number() function

    1st row_number() with partition by docno and date1

    2nd row_number() with partition by docno.

    But i could not order them in proper sequence. Is there any other way we can order such values.

    Thanks

    Rohit

    You don't need 2 row_number(), one will be enough.

    ROW_NUMBER() needs an ORDER BY and you can include multiple columns on it.

    You can use ORDER BY at the end of the query and you can use column alias on the ORDER BY.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply