# Thread: Help with Order By

1. Registered User
Join Date
Sep 2005
Posts
220

## Unanswered: Help with Order By

I have a list of part numbers that are alphanumeric, and quantities. I need to sort this list by:

1.Partnumber with Qty <>0
1a. First positive Qty then negative Qty
2. Partnumber with Qty 0

Example:
Partnumber 1234567 with Qty 2
Partnumber 1234567 with Qty -1
Partnumber 1234568 with Qty 1
Partnumber 0123456 with Qty 0

How do I do this?

2. Registered User
Join Date
Jul 2003
Posts
2,296
looks like you want to start by ordering by partnumber and then qty desc.
write a query like that, post it, and then let us know what else you need

3. Registered User
Join Date
Dec 2003
Posts
1,074
Code:
```select partnumber, qty, 1 As Sort
from table
where qty <> 0
union all
select partnumber, qty, 2 As Sort
from table
where qty = 0
order by Sort, qty desc```
or

Code:
```select partnumber, qty, (case when qty = 0 then -9999999999 else qty end) as sort
from table
order by (case when qty = 0 then -9999999999 else qty end) desc```
-cf

4. Registered User
Join Date
Sep 2005
Posts
220
I think I am close, but the 1 and the 2 both show up in the results, and I don't want that.

Code:
```SELECT
p.ver,
p.loc,
p.splrcode_dlr,
REPLACE (REPLACE (p.item, '@', '.'), '#', ' ') item,                   -- replace @ -> . & # -> _ to undo the substitution made earlier
DECODE((SIGN(NVL(p.sofqty, 0))),
-1,  '-' || LPAD( TO_CHAR(   ABS(ROUND(p.sofqty, 2)*100))   , 7, '0'),
1,  LPAD( TO_CHAR(   ROUND(p.sofqty, 2)     *100), 8, '0'),
) sofqty,
p.ordertype,
SUBSTR(p.SOFMSG,1,29) SOFMSG
,1 AS SORT
FROM pac.SOF p,
(           -- compare the loc and p_SOFName columns, check for switch p_SOFAvailSw.
-- If all the switch'es are 1 then run scrip above
SELECT
l.p_SOFName,
SUM(SIGN(l.p_SOFAvailSw)) su,
COUNT(l.p_SOFName) cn
FROM loc l,
(SELECT
l.loc,
l.p_SOFName,
l.p_SOFAvailSw
FROM stsc.loc l
WHERE l.loc = '&1' -- =======> dealer
) sl
WHERE l.p_SOFName = sl.p_SOFName
GROUP BY l.p_SOFName
) i
WHERE p.sofloc IN  -- spool out data for the location group --------
(SELECT l.loc FROM loc l,
(SELECT l.loc, l.p_SOFName, l.p_SOFAvailSw
FROM stsc.loc l WHERE l.loc = '&1' ) sl
WHERE l.p_SOFName = sl.p_SOFName)
AND p.sofdate = TO_DATE('&3', 'dd/mm/yy')
AND p.difdate = TO_DATE('&4', 'dd/mm/yy')
AND i.cn = i.su
AND p.sofqty <> 0
UNION ALL
SELECT
p.ver,
p.loc,
p.splrcode_dlr,
REPLACE (REPLACE (p.item, '@', '.'), '#', ' ') item,                   -- replace @ -> . & # -> _ to undo the substitution made earlier
DECODE((SIGN(NVL(p.sofqty, 0))),
-1,  '-' || LPAD( TO_CHAR(   ABS(ROUND(p.sofqty, 2)*100))   , 7, '0'),
1,  LPAD( TO_CHAR(   ROUND(p.sofqty, 2)     *100), 8, '0'),
) sofqty,
p.ordertype,
SUBSTR(p.SOFMSG,1,29) SOFMSG
,2 AS SORT
FROM pac.SOF p,
(           -- compare the loc and p_SOFName columns, check for switch p_SOFAvailSw.
-- If all the switch'es are 1 then run scrip above
SELECT
l.p_SOFName,
SUM(SIGN(l.p_SOFAvailSw)) su,
COUNT(l.p_SOFName) cn
FROM loc l,
(SELECT
l.loc,
l.p_SOFName,
l.p_SOFAvailSw
FROM stsc.loc l
WHERE l.loc = '&1' -- =======> dealer
) sl
WHERE l.p_SOFName = sl.p_SOFName
GROUP BY l.p_SOFName
) i
WHERE p.sofloc IN  -- spool out data for the location group --------
(SELECT l.loc FROM loc l,
(SELECT l.loc, l.p_SOFName, l.p_SOFAvailSw
FROM stsc.loc l WHERE l.loc = '&1' ) sl
WHERE l.p_SOFName = sl.p_SOFName)
AND p.sofdate = TO_DATE('&3', 'dd/mm/yy')
AND p.difdate = TO_DATE('&4', 'dd/mm/yy')
AND i.cn = i.su
AND p.sofqty = 0
ORDER BY SORT, sofqty DESC```

5. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713

Try:

COL SORT NOPRINT

6. Registered User
Join Date
Sep 2005
Posts
220
Thanks to everyone for your feedback. It is truly appreciated.

The only issue I have now is that the item number is Varchar(2) and does not get sorted correctly.

7. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713

Replace:
Code:
```...
DECODE((SIGN(NVL(p.sofqty, 0))),
-1,  '-' || LPAD( TO_CHAR(   ABS(ROUND(p.sofqty, 2)*100))   , 7, '0'),
1,  LPAD( TO_CHAR(   ROUND(p.sofqty, 2)     *100), 8, '0'),
) sofqty,```
With:
Code:
```...
DECODE(NVL(p.sofqty, 0),0,'00000000',TO_CHAR(ROUND(p.sofqty,2)*100,'S0000000')) sofqty,```

8. Registered User
Join Date
Sep 2005
Posts
220
This is VERY close. What I need is this:

format for numbers >= 0 should not have a sign
format for numbers < 0 should have a negative sign

QTY FORMAT

3 00000300

0 00000000

-3 -0000300

9. Registered User
Join Date
Sep 2005
Posts
220
This is VERY close. What I need is this:

format for numbers >= 0 should not have a sign
format for numbers < 0 should have a negative sign

Code:
```QTY            FORMAT

3               00000300

0               00000000

-3              -0000300```

10. Registered User
Join Date
May 2004
Location
Dominican Republic
Posts
721
It is sad there is no sample data and output wanted in this problem.. but how about something like.. select qty, decode( greatest( qty, 0 ), 0, '-' ) || to_char( qty*100, 'fm000000' ) format ....

#### Posting Permissions

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