CODEWITHSUNDEEP
sqloracle-databasesql-order-by
Nomad
Nomad

Reputation: 268

SQL order by 100% not ordering first

I have a query that shows my counter's accuracy and I'm doing a simple order by accuracy. But, what is happening it is ordering correctly BUT 100% appears at the bottom and not in correct order. I have moved the order by, but I may be missing something?

QUERY:

select counted_by_user_id, total, defects, trunc((total-defects)/total*100,2)||'%' as accuracy from
(
select counted_by_user_id, sum(locations) as total, sum(numberOfDefectBins) as defects from
(
select counted_by_user_id, locations, numberOfDefectBins from
(
select trunc(ical.last_updated_date_utc) as CountDate,ip.process_name,ipl.icqa_process_id,ical.counted_by_user_id,count(*) as locations, (         
SELECT COUNT(iid.icqa_ical_detail_id)        
FROM icqa_process_locations ipl1,          icqa_count_attempt_logs ical1,          icqa_ical_details iid        
WHERE ipl1.icqa_process_id =   ipl.icqa_process_id 
AND ical1.icqa_count_attempt_id = ipl1.icqa_count_attempt_id     
AND ical1.counted_by_user_id =  ical.counted_by_user_id      
AND iid.icqa_count_attempt_log_id = ical1.icqa_count_attempt_log_id     
AND iid.is_defective = 'Y'   and  trunc(ipl1.last_updated_date_utc) = trunc(sysdate)) as numberOfDefectBins     
from  icqa_count_attempt_logs ical left join icqa_process_locations ipl  
on  ical.icqa_count_attempt_id = ipl.icqa_count_attempt_id 
left join ICQA_PROCESSES ip on ipl.icqa_process_id= ip.icqa_process_id 
where  trunc(ical.last_updated_date_utc) = trunc(sysdate)  
AND     ical.counted_by_user_id IS NOT NULL 
group by trunc(ical.last_updated_date_utc), ip.process_name, ipl.icqa_process_id, ical.counted_by_user_id 
order by ical.counted_by_user_id
))
group by counted_by_user_id
)
order by accuracy desc;

RESULT:

counted_by_user_id  total   defects accuracy
dggonza 346 1   99.71%
giermanc    225 1   99.55%
kylecoll    659 4   99.39%
manansal    71  1   98.59%
jssuarez    271 5   98.15%
jhheredi    464 10  97.84%
tabilinl    185 4   97.83%
darinc  102 3   97.05%
tostab  484 18  96.28%
alicmena    25  1   96%
reyesk  733 31  95.77%
genej   478 22  95.39%
yadirac 73  4   94.52%
lhherold    505 28  94.45%
anamarih    465 30  93.54%
pineiror    380 25  93.42%
nallelys    349 31  91.11%
almquij 112 12  89.28%
kustance    357 50  85.99%
arteagaa    54  12  77.77%
gardne  848 0   100%
willij  5   0   100%
castnera    21  0   100%
pbarbara    43  0   100%
caudilr 493 0   100%
jennifei    27  0   100%

Upvotes: 1

Views: 164

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Of course. Because you are sorting a string not a number.

There are multiple solutions:

order by length(accuracy) desc, accuracy;

is probably the easiest.

Some others:

order by cast(replace(accuracy, '%', '') as float);

order by (total-defects)/total;

order by (case when accuracy = '100%' then 1
               when accuracy >= '10%' then 2
               else 3
          end), accuracy desc

Upvotes: 4

Related Questions