0

I'm trying to sort a table of dates into ascending or descending order, but I can't get it to do either. The query isn't giving a syntax error so I don't think it's that.

Server version: 5.7.28-0ubuntu0.18.04.4 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

mysql> select * from elections order by 'last_election' ASC;
+--------+-----------------+---------------+
| row_id | jurisdiction_id | last_election | 
+--------+-----------------+---------------+
|      1 |               3 | 2018-11-24    |
|      2 |               2 | 2019-03-23    |
|      4 |               5 | 2018-03-17    |
|      5 |               4 | 2017-11-25    |
|      6 |               1 | 2016-10-15    |
|      7 |               6 | 2017-03-11    |
|      8 |               7 | 2018-03-03    | 
|      9 |               8 | 2016-08-27    | 
|     11 |               9 | 2019-05-18    | 
+--------+-----------------+---------------+
9 rows in set (0.00 sec)

Previous / other similar questions all say, "Your field type is text or varchar, duh!" but the field type is definitely date: 

+----------------------+---------+------+-----+---------+----------------+
| Field                | Type    | Null | Key | Default | Extra          |
+----------------------+---------+------+-----+---------+----------------+
| row_id               | int(11) | NO   | PRI | NULL    | auto_increment |
| jurisdiction_id      | int(11) | NO   | UNI | NULL    |                |
| last_election        | date    | YES  |     | NULL    |                |
+----------------------+---------+------+-----+---------+----------------+

How do I query the table so that last_election is in order?

AK_oz
  • 56
  • 12
  • 2
    You're attempting to sort by a constant (`'last_election'`). Remove the quotes around `last_election` or change them to backticks – Nick Jan 10 '20 at 06:57

2 Answers2

1

I think it should be:

select * from elections order by `last_election` ASC

Note type of quotes

Because ' ' are for strings and backticks are for column names. So I guess you are trying to sort them by string that is not changing - therefore it works same as not having order by at all

ysth
  • 96,171
  • 6
  • 121
  • 214
NoOorZ24
  • 2,914
  • 1
  • 14
  • 33
  • `select * from elections order by last_election ASC` should also work – NoOorZ24 Jan 10 '20 at 06:49
  • @AK_oz I'm working with mssql and there ordering by date is totally fine. Seems odd for it not to work in mysql – NoOorZ24 Jan 10 '20 at 06:54
  • Yeah i think the thing that's confusing me most is that it isn't giving me a syntax error, AND it's not treating fields as a string either - it's doing *nothing* – AK_oz Jan 10 '20 at 06:55
  • 2
    This correctly identifies the problem - sorting by a constant string, but double quotes also make strings. Use backticks (or just the plain column name) – ysth Jan 10 '20 at 06:58
  • using ` instead of ' or " seems to get it to have an effect - that edit made it work too. Now there are three solutions that all work – AK_oz Jan 10 '20 at 06:59
-1

Your query seems okay

Try below query.

select * from elections order by date(last_election) ASC;

If its work try to drop table and recreate it with proper field types.

Himanshu Patel
  • 39
  • 1
  • 9
  • This works, for some reason. When you say 'recreate it with proper field types' what field types are you recommending over 'date' for a field that stores dates? – AK_oz Jan 10 '20 at 06:53
  • The query is not ok and there is no need for OP to recreate their table. – Nick Jan 10 '20 at 07:01