CODEWITHSUNDEEP
sqlpostgresql
user3934365
user3934365

Reputation: 7

Postgres order by clause returning out of sequence number

I have this postgres sql query: 

select Symbol,Date,Year,DayofYear Close 
from DailyPricesAMEX 
where symbol = IBM 
order by DayofYear DESC;

The output from the query is:

Symbol  Year  DayofYear
IBM     2014  99
IBM     2014  98
IBM     2014  97
IBM     2014  9
IBM     2014  89
IBM     2014  88

What I want returned is:

Symbol  Year  DayofYear
IBM     2014  99
IBM     2014  98
IBM     2014  97
IBM     2014  89
IBM     2014  88
IBM     2014  9

I'm stuck, anyone got any ideas.

Upvotes: 0

Views: 36

Answers (1)

chresse
chresse

Reputation: 5815

it seems to me like DayofYear is saved as a VARCHAR. Try following:

select Symbol,Date,Year,DayofYear Close 
from DailyPricesAMEX 
where symbol = IBM 
order by DayofYear::integer DESC;

http://sqlfiddle.com/#!15/40a2f/2

HINT: it would be better to change the type of your column to integer. Therefore you can use the following:

ALTER TABLE DailyPricesAMEX ALTER COLUMN DayofYear TYPE integer USING (DayofYear::integer);

Upvotes: 1

Related Questions