Showing error for order by

Question

I created table and I inserted some values.

create table deposit1_groupc_tja05
(
  Account_id number(25),
  Account_type varchar(25),
  Balance number(9,2) not null,
  Transaction_amount number(9,2) not null,
  Transaction_Date varchar(50),
  Transaction_type varchar(25)
)

Now I need to order them by Transaction_Date? How do I alter that?

`SELECT * FROM deposit1_groupc_tja05 ORDER BY Transaction_Date` — Radim Bača, Aug 28 '17 at 06:45
Rows in an RDBMS represent unordered sets. If you want an order, you must include one when querying the data set. Incidentally, tables named in the way yours is are often exhibiting symptoms of poor design — Strawberry, Aug 28 '17 at 06:46
Are you saying you need them in the table in order? Or like the comment above in order when they are selected? — ChrisM, Aug 28 '17 at 06:47
@Pravin : Why are you storing `Transaction_Date` as `varchar`? — Ullas, Aug 28 '17 at 06:48
Show us what those text transaction dates look like. By the way, stop storing dates as text. — Tim Biegeleisen, Aug 28 '17 at 06:48
@Strawberry I interpret the OP as that it stored dates in some text format, and now wants to order by them, but can't. — Tim Biegeleisen, Aug 28 '17 at 06:49
I got it as String.Is there any way to edit datatype without truncating the table data? — Pravin, Aug 28 '17 at 06:50
You can create a new column. Then, once you have populated that column, delete the old column and, if necessary, rename the new one — Strawberry, Aug 28 '17 at 06:52
@Pravin https://stackoverflow.com/questions/7090231/how-to-convert-a-varchar-column-type-to-date-type-without-losing-the-dates — Radim Bača, Aug 28 '17 at 06:53
You are unclear. Please tell us exactly what you are trying to do, what you did and what is going wrong. Please read & act on [mcve]. Except that this is almost certainly a faq that you can find by googling your title. Beware there are a lot of wrong answers, see the duplicate link. — philipxy, Aug 28 '17 at 21:30

Bill Karwin · Accepted Answer · 2017-08-28T14:19:09.237

In MySQL's default storage engine, InnoDB, the table is always stored ordered by its primary key, which is the clustered index.

You don't declare a primary key in the example you show, so InnoDB will be ordered by an invisible primary key, so the storage order will be the order in which you inserted rows.

You can use ALTER TABLE to declare a primary key. This will reorder the storage by primary key. If you use Transaction_Date, a varchar, it will be ordered alphabetically, not by date. You should use a DATE data type if you want it ordered by date.

If you want a given query result set to be ordered by another column besides the primary key, then just use an ORDER BY clause. You can get a result set ordered differently from the table's storage order.

If you don't use an ORDER BY clause in your query, the result set has no guaranteed order in general (and per the SQL standard), and it's up to the implementation. In the case of InnoDB, there's still no guarantee it will always work this way, but practically, in current versions, a query with no ORDER BY returns rows in the order it reads them from the index (which may be the clustered index / primary key).

The implementation is not the abstraction. The OP doesn't say they want the *storage* ordered. And there is no guarantee that a result set for a query without ORDER BY is in any order regardless of storage order. — philipxy, Aug 28 '17 at 10:29
@philipxy, Indeed, the OP's question is vague, and I guess we're not going to get any clarification from them. — Bill Karwin, Aug 28 '17 at 14:15

score 0 · Answer 2 · edited Aug 28 '17 at 21:19

0

There is no way to order the table in the way you are describing.

If you wanted to select the data in order then use the following

SELECT * 
FROM deposit1_groupc_tga05
ORDER BY Transaction_Date

You should also create the table with Transaction_Date as datetime

edited Aug 28 '17 at 21:19

answered Aug 28 '17 at 06:51

ChrisM

1,576
6
18
29

score 0 · Answer 3 · answered Aug 28 '17 at 09:42

If you can't change the Transaction_Date column to datetime, then you need to cast the date in the query like this:

SELECT * FROM deposit1_groupc_tga05
ORDER BY STR_TO_DATE(Transaction_Date, '%c/%e/%Y %H:%i')

Or depending on the format the date is inserted, you need to change the format string.

Showing error for order by

3 Answers3