Tutorial :Order mysql results without identifier



Question:

Usually I would have a table field called ID on auto increment. That way I could order using this field etc.

However I have no control over the structure of a table, and wondered how to get the results in reverse order to default.

I'm currently using

$q = mysql_query("SELECT * FROM ServerChat LIMIT 15");  

However like I said there is no field I can order on, so is there a way to tell mysql to reverse the order it gets the results? I.e last row to first row instead of the default.


Solution:1

MySQL supports ordering by ordinal column position:

SELECT * FROM ServerChat ORDER BY 1 DESC LIMIT 15  

But IIRC this usage of ORDER BY is deprecated in the SQL standard. Don't be surprised if some RDBMS vendors discontinue support for it (eventually).

In general, it's better to know your table structure.


Solution:2

No. Without a field to order there is no way


Solution:3

You are in fact getting your results in what is known as "table order", which may look like it's in the order that data was added to the table but that order is not stable. There are a number of operations that can change the order you are receiving results without changing the data in the table itself.

To reproduce the sort of order you are seeing I'd suggest adding a column to your table of type ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP. This will give you a column to specifically order on and therefore reverse that order. You should probably add an index on that column as well if this operation is a frequent one.


Solution:4

According to this article, SQL-92 allows the user to query table structure information from a "well known" view or table called INFORMATION_SCHEMA. SQL-92 is supported by MySQL 5.0 and up.

Example/Excerpt:

SELECT table_name, column_name, is_nullable, data_type, character_maximum_length  FROM INFORMATION_SCHEMA.Columns  WHERE table_name = 'employees'  

So you could use the list of column names to allow the user to select which column it's ordered by, then use this SO answer to figure out how to build the dynamic SQL so you execute the query correctly.

I've not tried it with MySQL, but the method certainly makes sense to me.


Solution:5

Your table must have some unique index specified. It doesn't have to be named ID but it's typically required and likely what's determining the order returned currently. What is it? Whatever it is, it's my understanding that you should be able to do an ORDER BY ... DESC (or if that doesn't work, ASC) like this example with the unique identifier hash:

$q = mysql_query("SELECT * FROM ServerChat ORDER BY `hash` DESC LIMIT 15");  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »