Order By - reorder recordset 2

[Andrzejek]

I have this SQL to get some info from my table:

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:
[tt]
PSMGRS_SQUADNO PSMGRS_FULLNAME
03100 David R.
03101 William L.
03102 Patricia G.
03103 Christine E.
03901 John R.[/tt]

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

Like this:
[tt]
PSMGRS_SQUADNO PSMGRS_FULLNAME
03901 John R.
03100 David R.
03101 William L.
03102 Patricia G.
03103 Christine E.
[/tt]

Have fun.

---- Andy

 

[Turkbear]

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

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]

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]

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

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]

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]

If you want your last record first, how about:

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

 

[carp]

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

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]

Hi,
Gee carp , that looks a little familar

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)

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]

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]

Thank a lot, both: Turkbear's

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

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

solutions work

Have fun.

---- Andy

 

[Turkbear]

Hi,
Thanks Andy, glad to help.

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

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

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





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