INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...I train people in ACCESS. I make sure they know about these forums, give them some training on how to use the site, and have a shortcut to it on their PC's..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Oracle: Oracle release - 9i and earlier Forum

Order By - reorder recordset(2)

CODE

SELECT * FROM PSMANAGER_INFORMATION
WHERE (PSMGRS_SQUADNO BETWEEN '03100' AND '03149')
OR (PSMGRS_SQUADNO = '03901')
ORDER BY PSMGRS_SQUADNO

So I get the data like this:

PSMGRS_SQUADNO  PSMGRS_FULLNAME
03100            David R.
03101            William L.
03102            Patricia G.
03103            Christine E.
03901            John R.

How can I modify my SQL to get the last record (03901 John R.) first?  (I do know it is always 03901)

Like this:

PSMGRS_SQUADNO  PSMGRS_FULLNAME
03901            John R.
03100            David R.
03101            William L.
03102            Patricia G.
03103            Christine E.

Have fun.

---- Andy

Turkbear (TechnicalUser)

21 Jan 10 12:07

Hi,
In spite of my curiosity as to why, try this:

CODE

SELECT * FROM PSMANAGER_INFORMATION
WHERE PSMGRS_SQUADNO = '03901'
UNION ALL
(SELECT * FROM PSMANAGER_INFORMATION
WHERE (PSMGRS_SQUADNO BETWEEN '03100' AND '03149')
ORDER BY PSMGRS_SQUADNO)

It may work to place 03091 before the ordered result of the union query, Maybe - never tried.

To Paraphrase:"The Help you get is proportional to the Help you give.."

Andrzejek (Programmer)

21 Jan 10 12:23

Well, this is a good question: why, because the boss wants it this way.

And your suggestion does not work

You can UNION or UNION ALL, but the Order By is after the Union, so no matter what you Union, the Order By will sort the data one way after the Union is done.

Any other thoughts?

Have fun.

---- Andy

Turkbear (TechnicalUser)

21 Jan 10 13:11

Hi,
OK, ( I thought that the Order By would only apply to the second record set - hence the parens) so maybe an inline view:

CODE

SELECT * FROM PSMANAGER_INFORMATION
WHERE PSMGRS_SQUADNO = '03901'
UNION ALL
SELECT * FROM
(Select * from PSMANAGER_INFORMATION
WHERE (PSMGRS_SQUADNO BETWEEN '03100' AND '03149'
ORDER BY PSMGRS_SQUADNO)

To Paraphrase:"The Help you get is proportional to the Help you give.."

cmmrfrds (Programmer)

21 Jan 10 13:23

select
*
from
(
SELECT '1' as skey,
PI.* FROM PSMANAGER_INFORMATION PI
WHERE PI.PSMGRS_SQUADNO = '03901'
UNION ALL
SELECT '2' as skey,
PI.* FROM PSMANAGER_INFORMATION PI
WHERE (PI.PSMGRS_SQUADNO BETWEEN '03100' AND '03149')
)
ORDER BY skey, PSMGRS_SQUADNO

carp (MIS)

21 Jan 10 20:12

If you want your last record first, how about:

CODE

SELECT * FROM PSMANAGER_INFORMATION
WHERE (PSMGRS_SQUADNO BETWEEN '03100' AND '03149')
OR (PSMGRS_SQUADNO = '03901')
ORDER BY PSMGRS_SQUADNO DESC;

carp (MIS)

21 Jan 10 20:17

OK, disregard last. That will only work if 03901 is the highest value.
Instead, how about

CODE

SELECT * FROM PSMANAGER_INFORMATION
WHERE PSMGRS_SQUADNO = '03901'
UNION ALL
SELECT * FROM
(
Select * from PSMANAGER_INFORMATION
WHERE PSMGRS_SQUADNO BETWEEN '03100' AND '03149'
AND PSMGRS_SQUADNO != '03901'
ORDER BY PSMGRS_SQUADNO
)

Turkbear (TechnicalUser)

21 Jan 10 21:22

Hi,
Gee carp , that looks a little familar wink

Quote (turkbear):

SELECT * FROM PSMANAGER_INFORMATION
WHERE PSMGRS_SQUADNO = '03901'
UNION ALL
SELECT * FROM
(Select * from PSMANAGER_INFORMATION
WHERE (PSMGRS_SQUADNO BETWEEN '03100' AND '03149'
ORDER BY PSMGRS_SQUADNO)

Quote (carp):

SELECT * FROM PSMANAGER_INFORMATION
WHERE PSMGRS_SQUADNO = '03901'
UNION ALL
SELECT * FROM
(
Select * from PSMANAGER_INFORMATION
WHERE PSMGRS_SQUADNO BETWEEN '03100' AND '03149'
AND PSMGRS_SQUADNO != '03901'
ORDER BY PSMGRS_SQUADNO
)

'All great minds think alike....'

To Paraphrase:"The Help you get is proportional to the Help you give.."

carp (MIS)

22 Jan 10 7:58

EXTREMELY familiar! In fact, so familiar you might think it was a cut/paste with a couple of additions! The only differences being (1)I dropped the subquery one line below the opening parenthesis (makes all the difference in the world, you know!) and (2) added a condition to the subquery to avoid duplicate rows. Aside from that, it was a shameless lift - I was starting to enter the query and realized there was one that was very close already in play; my inner lazy guy kicked in. I believe this puts me into the running for the 2010 Joe Biden award.

Andrzejek (Programmer)

22 Jan 10 11:27

Thank a lot, both: Turkbear's

CODE

SELECT * FROM PSMANAGER_INFORMATION
WHERE PSMGRS_SQUADNO = '03901'
UNION ALL
SELECT * FROM
(Select * from PSMANAGER_INFORMATION
WHERE PSMGRS_SQUADNO BETWEEN '03100' AND '03149'
ORDER BY PSMGRS_SQUADNO)

and cmmrfrds's

CODE

solutions work

Have fun.

---- Andy

Turkbear (TechnicalUser)

22 Jan 10 11:49

Hi,
Thanks Andy, glad to help.

To carp
No problem, but , by the way your addition

Quote (carp):

(2) added a condition to the subquery to avoid duplicate rows.

seemed unneeded since '03901' is not between
'03100' and '03149'
wink

To Paraphrase:"The Help you get is proportional to the Help you give.."

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Oracle: Oracle release - 9i and earlier

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

Order By - reorder recordset(2)

CODE

CODE

CODE

CODE

CODE

Quote (turkbear):

Quote (carp):

CODE

CODE

Quote (carp):

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!