SQL Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: SQL Question

  1. #1
    Join Date
    Jan 2001
    Posts
    515

    Question

    I am trying to create a query that will return two specific records first and then follow them by the rest of the records that match the query. For example:

    select acno from rcb_acct
    where acno in ('555555666666666','333333333333333')
    and lgon_pref = 'MYN'
    order by acno
    union
    select acno from rcb_acct
    where lgon_pref = 'MYN'
    order by acno
    /

    I want the first two records that match my first select statement and then the rest that match the second selec statement. The problem I am having with the above query is that it doesn't like the order by clause. I need the results of each query sorted seperately. Is there a way I could run two queries and concatenate the results together?

  2. #2
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Cool try this



    take off the order by clause and see what you get.

    - Magnus

  3. #3
    Join Date
    Mar 2001
    Posts
    22

    use DECODE

    you can try this

    select acno from rcb_acct
    where lgon_pref = 'MYN'
    order by
    decode(acno ,
    '555555666666666','1'
    '333333333333333','2',acno) ;

    assuming acno is always greater than '1','2' so that default value from decode is the acno and will be ordered after '1' and '2'.

    OR

    if you want to sort by two groups of union, try this

    select acno,1 ordering from rcb_acct
    where acno in ('555555666666666','333333333333333')
    and lgon_pref = 'MYN'
    union
    select acno,2 ordering from rcb_acct
    where lgon_pref = 'MYN'
    order by ordering, acno



    hptse

  4. #4
    Join Date
    Dec 2000
    Posts
    126
    try this

    select acno , 1 as ord
    from rcb_acct
    where acno in ('555555666666666','333333333333333')
    and lgon_pref = 'MYN'
    order by acno , ord
    union
    select acno, 2 as ord
    from rcb_acct
    where lgon_pref = 'MYN'
    order by acno , ord
    /

  5. #5
    Join Date
    Dec 2000
    Posts
    126
    sorry wrong order by statement
    the sql should read

    select acno , 1 as ord
    from rcb_acct
    where acno in ('555555666666666','333333333333333')
    and lgon_pref = 'MYN'
    order by ord, acno
    union
    select acno, 2 as ord
    from rcb_acct
    where lgon_pref = 'MYN'
    order by ord, acno
    /

  6. #6
    Join Date
    Mar 2001
    Posts
    22
    ORDER BY cannot be used in the sql statement of a UNION.

    It can be used only after the UNION, hence only one ORDER BY is allowed.
    hptse

  7. #7
    Join Date
    Jan 2001
    Posts
    23

    Thumbs up


    Complicating a little too much...

    I hope this will solve your problem.

    SELECT
    DECODE(acno,'555555666666666',1,'333333333333333',2,3) sort_order, acno
    FROM rcb_acct
    WHERE lgon_pref = 'MYN'
    ORDER BY sort_order, acno

    Cheers,
    Nanda



  8. #8
    Join Date
    Feb 2001
    Posts
    125
    Hi,


    select acno from rcb_acct
    where acno in ('555555666666666','333333333333333')
    and lgon_pref = 'MYN'
    -- removed order by clause from here; now it will work
    union
    select acno from rcb_acct
    where lgon_pref = 'MYN'
    order by acno


    Pls. check.


    P. Soni

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I just noticed this is the same query that I solved under a different title. Please try to keep the questions consolidated into a single post.

    - Chris

Posting Permissions

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


Click Here to Expand Forum to Full Width