Results 1 to 3 of 3

Thread: Sorting query

  1. #1
    Join Date
    Jul 2004
    Posts
    4

    Unanswered: Sorting query

    Hi All,
    Need your help in sorting the following result set.

    Unsorted records

    subpremise---premisename--thoroughfarenumber---thoroughfare
    FL 1----ABC------ 64-------HIGH RD
    'null'----'null'------ 84-------HIGH RD
    FL 1----'null'------64-------HIGH RD
    FLAT 3--'null'------84-------HIGH RD
    FLAT 4--'null'------84-------HIGH RD


    Can this be sorted into Sub Premise within Premise Name within Thoroughfare Number within Thoroughfare where a null value is sorted before a populated field and populated fields are sorted in ascending order


    Sorted records

    subpremise---premisename--thoroughfarenumber---thoroughfare
    FL 1----'null'------64-------HIGH RD
    FL 1----ABC------ 64-------HIGH RD
    'null'----'null'------ 84-------HIGH RD
    FLAT 3--'null'------84-------HIGH RD
    FLAT 4--'null'------84-------HIGH RD

    Thanks in Advance

    Regards

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    order by thoroughfare nulls first, thoroughfarenumber nulls first, premisename nulls first, subpremise nulls first

  3. #3
    Join Date
    Jul 2004
    Posts
    6
    use oracle function "nvl" to replace null to 0 in order by clause.
    e.g.

    select * from mytable order by nvl(col1,0),nvl(col2,0),nvl(col3,0)

Posting Permissions

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