"SELECT * FROM view_table WHERE cause" => But the result always reorder

Question

In SELECT * FROM view_table The result always is

-----------------------
|| id  ||   author   ||
-----------------------
||  1  ||    a       || <--
||  1  ||    c       || <--
||  1  ||    b       || <--
||  2  ||    d       ||
||  3  ||    e       ||

But when SELECT * FROM view_table WHERE id=1 the result is

-----------------------
|| id  ||   author   ||
-----------------------
||  1  ||    a       ||
||  1  ||    b       ||
||  1  ||    c       ||

Or

-----------------------
|| id  ||   author   ||
-----------------------
||  1  ||    b       ||
||  1  ||    c       ||
||  1  ||    a       ||

Or

-----------------------
|| id  ||   author   ||
-----------------------
||  1  ||    a       ||
||  1  ||    c       ||
||  1  ||    b       ||

Or

..

I want to SELECT * FROM view_table WHERE id=1 and get the results in the same order as SELECT * FROM view_table. How to solve it

Thank You.

Simply you can't , as long as you do not ask for a precise order (via order by), you can't expect the same order across different select queries — Gar, May 16 '19 at 08:32
Have a read through https://stackoverflow.com/questions/49815703/what-is-the-default-order-by-for-a-mysql-innodb-query-that-omits-the-order-by — Nigel Ren, May 16 '19 at 08:36

score 3 · Accepted Answer · answered May 16 '19 at 08:31

3

The order of results is never guaranteed unless you use an ORDER BY clause. How you want to order it, is up to you.

For example,

SELECT * 
FROM view_table 
WHERE id = 1
ORDER BY author

This will sort it based on the author's name (from A to Z, or Z to A if you use ORDER BY author DESC).

Alternatively you can add a Created column and then order by that column instead, which holds the timestamp when it was created. That could mimic an order that you want to keep.

answered May 16 '19 at 08:31

Qirel

25,449
7
45
62

Thanks sir. but Can i add timestamp in view table? – Doggo May 16 '19 at 08:51
3

You can add it directly to your table. Make it default `CURRENT_TIMESTAMP`. That way, you will see when the row was created. Order your result by that. – Qirel May 16 '19 at 08:54

score 1 · Answer 2 · answered May 16 '19 at 08:32

1

You want to get the ordered values? It is easy just as below:

select * from view_table order by id,author
select * from view_table where id = 1 order by author

answered May 16 '19 at 08:32

Shawn.X

1,323
6
15

I want to get result order like select * from view_table – Doggo May 16 '19 at 08:37
1

When you execute "select * from view_table", the order is also random in fact, so you just need to use the order by clause to make the order fixed. – Shawn.X May 16 '19 at 08:40

score 1 · Answer 3 · answered May 16 '19 at 08:32

What you are seeing is a function of the fact that rows in a table have no implied order. If you want your results ordered in a particular way, you must describe that in the query. So for your first query, you would order by id and author, and for the second, order by author:

SELECT * 
FROM view_table
ORDER BY id, author

Query 2:

SELECT *
FROM view_table
WHERE id = 1
ORDER BY author

score 0 · Answer 4 · answered May 16 '19 at 08:31

0

research on order by

SELECT * FROM view_table WHERE id=1 order by author DESC

answered May 16 '19 at 08:31

LukeDS

141
8

score 0 · Answer 5 · answered May 16 '19 at 08:36

0

Sorting based on the author's name

FROM A To Z

SELECT * FROM view_table WHERE id = 1 ORDER BY author ASC

FROM Z to A

SELECT * FROM view_table WHERE id = 1 ORDER BY author DESC

answered May 16 '19 at 08:36

Ghanshyam Nakiya

1,602
17
24

score 0 · Answer 6 · answered May 16 '19 at 08:37

If you want to order data alphabitic order the you want use SELECT * FROM view_table WHERE id=1 order by author

When you run code each time you get different result. Because of in you table you dont use clustered index. Also if you use pramery key it is also create clustered index on you table. And than you get the same result every time otherwise not. Because without clustered index database doesnt save any order.

Note: you can only use one clustered index in a table. But you can use nonclustered index about 999 times.

score 0 · Answer 7 · answered May 16 '19 at 08:39

0

I would recommend looking into the ORDER BY SQL clause.

Using ORDER BY you can do the following:

SELECT * FROM view_table WHERE id=1 ORDER BY author

You can also use the ASC and DESC to sort the columns further.

answered May 16 '19 at 08:39

Declan

91
6

score 0 · Answer 8 · answered May 16 '19 at 08:45

0

Try This

For ascending order with limit

SELECT * FROM view_table WHERE id=1 ORDER BY author ASC [LIMIT 3]

or

For descending order with limit

SELECT * FROM view_table WHERE id=1 ORDER BY author DESC [LIMIT 3]

answered May 16 '19 at 08:45

SAVe

814
6
22

"SELECT * FROM view_table WHERE cause" => But the result always reorder

8 Answers8