Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Pennsylvania
    Posts
    9

    Unanswered: SQL 'Order By' problem

    Hi All,
    I have a very simple version of the query that I am running and the 'Order By' part does not seem to work. The query does return all the rows that I need. For the 'Order By' tried doing an Ascend and Descend and it has no effect. Here is the query...

    SELECT
    p.c1, p.c2,
    (seLECT ifnull(b8, 0) from lib2.file2
    WHERE b1 = p.c1 and b2 = p.c2 ) as c3
    FROM
    Lib1.file1 as p
    WHERE
    < have some conditions>
    order by c3

    where c1 and c2 are columns in Lib1.file1 table
    b1, b2 and b8 are columns in lib2.file2 table
    c3 is the column name for the sub query that returns one
    row and one column

    Also. tried using the column number for the 'Order By' and then I get a syntax error.

    If you might know what the error is, please let me know.

    Thanks.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Code:
    TableA
    
          VAR1   VAR2
    ---------- ----------
             1          2
             1          3
    
    TableB
    
          VAR1    VAR2   VAR3
    ---------- ---------- ----------
             1          2        NULL
             1          3          1
    
    select a.var1, a.var2, (select NVL(var3, 0) from tableB where var1 = a.var1 AND var2 = a.var2) As "NEW"
    from tableA a
    ORDER BY NEW DESC;
    ORDER BY "NEW" and ORDER BY 3 are also valid ORDER BY clauses.

    This solution has been tested on Oracle 9i and SQL Server 2000. To execute the query in SQL Server 2000, replace NVL with ISNULL.
    Last edited by r123456; 02-17-04 at 22:28.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Location
    Pennsylvania
    Posts
    9
    I am running this query on DB2 and the syntax that I am using is all working fine, it is just that the 'Order By' does not seem to work.

    Anyway thanks for taking the time to help out.

Posting Permissions

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