Sorting in Oracle SQL

Question

I have a select query in Oracle SQL which returns the table with the results

Location     count  
 site1       95000
 site2       556900
 site3       65600

I have then used the floor function (floor/1000) to obtain last 3 digits and append it with K+ using floor(count/1000) || 'K+' as count . Once done with that, am getting a table like this

Location    count  
site1       95K+
site2       556K+
site3       65K+

Now the issue is, when am sorting the result table using the order by count desc it is sorting like this

Location    count  
site1       95K+
site3       65K+
site2       556K+

The sorting is happening by considering the 1st digit I guess. I need the result to be generated like this while sorting in descending order.

Location    count  
site2       556K+
site1       95K+
site3       65K+

Is there a way to achieve this result? Any suggestions

ORDER BY the original count, have another column alias for the second "count" — jarlh
– jarlh, Commented Sep 11, 2018 at 12:35

Gordon Linoff · Accepted Answer · 2018-09-11 12:35:54Z

1

I believe that you can simply do:

order by count(*) desc

That is, even though you are modifying the count() value in the select, you can still order by the actual count.

answered Sep 11, 2018 at 12:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fahmi · Accepted Answer · 2018-09-11 12:37:12Z

1

Try below query:

select location,floor(count/1000)||'K' as count
from tablename
order by floor(count/1000) desc

answered Sep 11, 2018 at 12:37

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

1 Comment

Dessma Over a year ago

This should work. The reason behind the odd behavior in the original query is that by concatenating 'K' after your number, you are forcing the DBMS to consider the value a string, and when ordering strings, you just compare letters 1 by 1 instead of as whole like you would with numbers.

Salman Arshad · Accepted Answer · 2018-09-11 12:46:08Z

0

The formatted value is a string and it will be sorted as string (e.g. 10K+ comes before 2K+ in dictionary order sorted ascending). Place the formatted value in SELECT clause but order by original value in ORDER BY clause:

SELECT Location, FLOOR(count/1000) || 'K+' AS k_formatted_count
FROM ...
ORDER BY count DESC

edited Sep 11, 2018 at 12:46

answered Sep 11, 2018 at 12:40

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Comments

Littlefoot · Accepted Answer · 2018-09-11 19:11:40Z

Although you've already been told what to do (just to repeat: order by count(*) desc), here's an example which shows how you might deal with such values (combined of numbers and letters), generally:

you'd extract numeric part (I used REGEXP_SUBSTR, although simple SUBSTR + INSTR would work just fine in this example),
apply TO_NUMBER to it (otherwise you'd still be sorting strings and get a wrong result)

SQL> with test (loc, num) as
  2    (select 'site1',  95000 from dual union all
  3     select 'site2', 556900 from dual union all
  4     select 'site3',  65600 from dual
  5    )
  6  select loc,
  7         floor(sum(num) / 1000) || 'K+' cnt    --> SUM represents your COUNT
  8  from test
  9  group by loc
 10  order by to_number(regexp_substr(cnt, '^\d+')) desc;

LOC        CNT
---------- ------------------------------------------
site2      556K+
site1      95K+
site3      65K+

SQL>

Collectives™ on Stack Overflow

Sorting in Oracle SQL

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related