CODEWITHSUNDEEP
sqlpostgresql
Se Song
Se Song

Reputation: 1663

pgSQL order by desc not work correctly or what I did is wrong?

I have data store in a column as text

806,1250,1225,1080,1891,1878,1243,391,218,1505,1425,586,1801,860,323,1108,1130,1150,1060,1059

I want to select order by desc using this query

SELECT unnest(string_to_array(q.mycolumn, ',')) id FROM mytable q ORDER BY id DESC;

But the other now show correctly as bellow

"860" "806" "586" "391" "323" "218" "1891" "1878" "1801" "1505" "1425" "1250" "1243" "1225" "1150" "1130" "1108" "1080" "1060" "1059"

Upvotes: 0

Views: 61

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use cast to int to achieve the desired ordering. As you have it, the numbers are treated as characters.

SELECT unnest(string_to_array(q.mycolumn, ',')) id 
FROM mytable q 
ORDER BY cast(unnest(string_to_array(q.mycolumn, ',')) as int) DESC;

Upvotes: 1

Related Questions