Subselects in MySQL 4.0.x

[lovedaddy]

What I'm trying to do is do a select with a limit first, then order the result.

I'll try and explain with an example

Say I have a table with a single column, with data:
a,b,c,...,x,y,z.

If I did the query: select * from table limit 5,5 I'd get returned:

f,g,h,i,j

If i did a select * from table order by columnname desc limit 5,5 i'd get returned:

q,r,s,t,u.

What I'm trying to do is get the limit first, ie the data set:
f,g,h,i,j. Then sort them to get
j,i,h,g,f.

Is this possible? I've had a brief poke about with the IN function, but cannot seem to crack it.

Pointers would be appreciated.

 

[Jonty]

Hi lovedaddy

Just a flying visit, I'm afraid. I think you're spot on with your current SQL syntax, it's perhaps just the LIMIT clause which needs to be modified if there turns out to be no way execute the ORDER BY after the LIMIT.

So, in effect, your LIMIT clause would be constructed so as to anticipate the ORDER BY. Since the as the second parameter of LIMIT is the offset, which can be a negative value so as to work backwards from the end if needs be, there should hopefully be a solution.

Anyway, I'm rambling on. If you haven't already, the MySQL SELECT Page details a lot of what you may need to solve your problem.

Good luck

 

[wyrd_fish]

you need to find out haw many result there are:

$query_01 = "select * from table_name"
$result_01 = mysql_query($query_01);
$num_results = mysql_num_rows($result_01);

then sort of invert stuff:uhoh:

so you order desending and select the last few, so if you had 100 results you would order them

100
099
098
...
003
002
001

then to get the second lot of 5

limit 90,5


sorted

I had the same prob as you a while ago...

PS the numbers may need tweeking a bit, depending on where differnt things start counting from*, I vagly remember having a prob with this...

*ie. 0 or 1

 

[lovedaddy]

Big thanks lads! Much appreciated!

 

[wyrd_fish]

oh...

*blushes*

your too kind...