Results 1 to 5 of 5

Thread: sql query

  1. #1
    Join Date
    Jul 2003
    Posts
    46

    Unanswered: sql query

    Hi
    This is rather pure sql question but it's going to be executed by Oracle so i've decided to post it here.
    I have task to do:
    i have table with columns col1 (only two values 0 and 1),col2,col3 and i have to sort data in the following way:
    first come records which have col1=1 and col3 not null
    and others records have to be sorted like 'order by col1,col2,col3'
    How can i do it ?
    Any suggestion ?
    Thank you for your ideas
    szalas

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT * FROM TABLE_A
    WHERE COL1 = 1
    AND COL3 IS NULL
    UNION
    SELECT * FROM TABLE_A
    WHERE NOT EXISTS (SELECT * FROM TABLE_A
    WHERE COL1 = 1
    AND COL3 IS NULL
    )
    ORDER BY COL1,COL2,COL3
    /

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    anacedent, that looks weird

    in the first half of the union, you are getting all the rows where X

    in the second half of the union, you are getting all the rows where X doesn't exist

    let's say X finds some rows -- the first subquery will return them, and the second subquery won't return anything

    let's say X does not find any rows -- the first subquery returns nothing and the second returns all rows in the table

    there's nothing in the original question that stated you could only return some rows (which is the case if X is true)

    besides, you haven't really addressed the sort order problem

    gszalach, try this --

    select col1, col2, col3
    from yourtable
    order by
    case when col1=1 and col3 is not null then 0 else 1 end
    , col1, col2, col3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    You could use a function in the order by:

    SQL> create table x (col1 int, col2 int, col3 int);

    Table created.

    SQL> insert into x values (0,33,45);

    1 row created.

    SQL> insert into x values (0, 33, null);

    1 row created.

    SQL> insert into x values (1, 34, null);

    1 row created.

    SQL> insert into x values (0, 34, -1);

    1 row created.

    SQL> insert into x values (1, 34, -1);

    1 row created.

    SQL> select * from x
    2 order by case when col1=1 and col3 is not null then -1 else col1 end, col2, col3;

    COL1 COL2 COL3
    ---------- ---------- ----------
    1 34 -1
    0 33 45
    0 33
    0 34 -1
    1 34

    Is that what you wanted ?

    In general, if col1 were not constrained, you could do
    select * from x
    order by case when <condition> then -1 else 0 end,
    col1, col2, col3;
    Slightly less efficient in this case.

    HTH
    Al

  5. #5
    Join Date
    Jul 2003
    Posts
    46

    Re: sql query

    Hi,
    Thank you very much . You were very helpfull I appreciate this
    Szalas

Posting Permissions

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