CODEWITHSUNDEEP
phpmysql
Titan
Titan

Reputation: 6040

Order mysql results without identifier

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.

Upvotes: 2

Views: 192

Answers (5)

buley
buley

Reputation: 29208

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");

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562358

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.

Upvotes: 5

Jason D
Jason D

Reputation: 2303

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.

Upvotes: 0

Dan Head
Dan Head

Reputation: 2742

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.

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157897

No. Without a field to order there is no way

Upvotes: 0

Related Questions