Sorting in MySQL

Drax

Is it possible to sort a subset of mysql results when using the limit command?

Example:
select * from table order by name limit 5, 5

running this query seems to execute the select *, sort the result and then pick out the 5 records as specified in the limit statement.

What I need is the 5 records sorted after being extracted from the result.

Dont know if I am making sense...

Hobbes

Why not just select the 5 then sort via whatever coding you are doing?

akari no ryu

Drax wrote:

Is it possible to sort a subset of mysql results when using the limit command?

Example:
select * from table order by name limit 5, 5

running this query seems to execute the select *, sort the result and then pick out the 5 records as specified in the limit statement.

What I need is the 5 records sorted after being extracted from the result.

Dont know if I am making sense...

What do you want to do, select the first five results in the database?
You'd need subqueries for that.

Drax

ok, for example i have 20 items in my table, it has 3 columns: id, name and value.

I am using php to page the data on the screen.

for example I have 5 items per page and I want to display page 2.

I use: select id, name, value from table limit 5, 5
this give me my 5 results.

Now i want to be able to sort by column, namely id, name or value.

so i add to the query: select id, name, value from table order by value limit 5, 5

this is where my problem occurs - it will sort on the whole table as opposed to the subset of 5 items - sorting on the whole table will obviously yield different results.

Sorry if this is confusing.
Cheers.

akari no ryu

If you want to be able to sort the page on values rather than the data then what you have there isn't an SQL question, it's a php question. The array sorting functions are well documented.

Civilian_Target

Ah - well you can't do that in SQL - not as far as I know anyway. You'll have to get the 5 results and use PHP to sort them - which shouldn't be hard if you use usort()