||- This is a tricky SQL question!!!

sfullman

Here's one for an SQL expert:

For ease let's say I have a set of letters a-z, and I say:

"select * from alphabet order by letter ASC limit 3,5"

This of course is going to give me D, E, F, G, H

However, if I want to get an inverted result, and try this,

"select * from alphabet order by letter DESC limit 3,5"

I'm going to get W, V, U, T, S, instead of H, G, F, E, D (which is what I want). [Boy is it tough to think backwards!!!]

Is there any way, without serious analysis and counting and all, to NATIVELY get the inverted result H,G,F,E,D using the limit clause in the query?

Thanks from me and anyone else who will need this to have "sort links" at the head of tables which are subsets of records!!

Sam Fullman
Compass Point Media

Anon

You could count the result set with $rows=mysql_num_rows($result_handle);

$initial = 3;
$limit = 5;

$offset = $rows - ($initial + $limit);

Then for a descending sort your query limit would be "limit $offset, $limit"

Hope that still makes sense when I look at it in the morning ;-)

Victor Grey

Anon

Well, actually you'd have to run the query twice - once to count the whole result set, and then again with the limit clause.

Not sure whether this would be faster than looping through the whole result set and extracting your limited set with php code.

-VG

Anon

My suggestion :

"select * from alphabet order by letter ASC limit 3,5"

This of course is going to give me D, E, F, G, H

put the result in an array and reverse the array to get output H,G,F,E,D

not much of a hassle. faster operation. same query. reversing takes place in memory.

thanks.

Anon

Hi!

If you do have this alphabet example and nothing that serious, just do:
SELECT * from alphabet WHERE letter in ('H','G','F','E','D') ORDER BY letter desc;

If you want to list words, then do:
WHERE word LIKE 'H%' OR letter LIKE 'G%', etc

You can also try this:
WHERE letter BETWEEN 'D' AND 'H' since this will use native characterset C comparison functions and should theoretically work as well.

Is that what you're after?
Appreciate your feedback. By the way, I am working on a dictionary web site so I've considered many of similar things before, some of which are still giving me a headache.

Best regards,
Stanislav

Anon

Hi!

Looked again at your question. You probably already saw the answer in the previous question, just use WHERE letter > 'H', for instance and do the limit to get the number of letters you need. You will need to get the letter to start with, if you do reverse ordering you will have to compute the letter which is offset by 4 letters from H and then use this letter in the SQL statement above (plus reverse the sign < ). This should be relatively easy with php functions:
chr(ord('H') + 4)
or do it with MySQL functions, but this will make the SQL hard to port to other SQL servers.

Glad to be of help,
Stanislav

Anon

FYI,

Sam works on limit clause basis to get his result from alphabet table. in his e.g. it was 3,5 that gave him D,E,F,G,H
(assuming the table in one column and that is waht he wanted).

if the output he required was always DEFGH or HGFED he need not require a table in 1st place and a query to get the same thing everytime.
think about it !! just constants will do.

His idea is simple to reverse the output. he may use a diff. limit clause 7,4 or 12,8.
array will do the trick for him.

never use hardcoded values as far as possible in queries *

The code is vulnerable to change everytime something changes in specs.

INMHO
=brij.

Anon

Hi!

Well, I do agree your suggestion is better, however he wanted a pure SQL solution. In this respect we both failed :-)

Best regards,
Stas

Anon

Yeah, or if you dont want to use an array, you could do the whole thing in SQL, using a sub-select.

So the sub select gets the required alphabet bit, but in the wrong order, and the outer select orders it correctly:

SELECT FROM (SELECT FROM alphabet ORDER BY letter ASC LIMIT 3,5) AS mysubselect ORDER BY letter DESC

Some SQL flavours dont allow sub-selects, but this worked on my postgreSQL system.

Nojer2

Anon

I wouldn't go for sub-select even if the Database supported it. It just puts a lot of stress on the DB engine.
in this regard where there are 26 rows, in an alphabet table(assuming English), it is fast and quick to do it using sub-select.

consider this but :

not everyone understands sub-select easily. it is complicated.

MySQL does not support it. Not required too.

not using a sub-select can make ur code database independent. u can implement the same on MS SQL server, oracle, postgreSQL or
mighty MySQL too.

*** If u can solve the problem programatically do not bother to hassle the DB engine much by putting in extra pressure. DB engine has a much better work to do than sub-select. utterly useless INMHO 🙂)

Anon

Well, you're right, it's a tricky one.

However, it depends on what SQL server you are using. If you use postgresql, you can write a function that does this. MySQL has support for functions as well;

http://www.mysql.com/doc/A/d/Adding_functions.html

MySQL does not currently support sub-selects, but if you have this available it should be solvable.

Alternately, you can ofcourse select the data into a temp table, and re-select/sort them from there.

Anon

I'm no optimisation expert, but surely you have that the wrong way round.

In the case of a small alphabet example it's better to use an array and not to hassle the DB engine....

...but if we're talking thousands of records in a big chunky database, then you wouldn't want PHP to start loading it all into an array.

Another advantage of this SQL approach is that you avoid creating a second representation of your record structure. If you have a complex structure (with assorted data types, objects etc) it would be a pain setting up an array structure to dump all the data in, and it would make it more difficult to change that structure.

Subselects are not portable to MySQL this is certainly a problem. Someone else was saying, you can always select 'into' a temporary table, and then perform the outer select operations on that, if you are using MySQL (or want to keep your code portable)

I did this with MySQL system once, but if your doing user specific queries, you have to be careful to avoid concurrancy issues. Slap a session ID on the temporary table name.

Nojer2

Anon

I think that looks like the best suggestion so far.

Make sure however to take into account that dividing the query into pages may leave a short page at the end. Reversing the query in this situation wouldn't give the desired results.

You'll want to divide the results by the number of record per page and find the remainder... looks like you may have to use some complicated counting and calculating after all.

sfullman

Boy did this question get some responses!!!

The example I used with alphabet letters was an oversimplification. Of course in that example (esp. with constants) you'd want to manipulate an array. However the more typical situation is when someone looks for companies where company name is ACME. There are say 500 ACME companies in the database, and I configure page to show 20 records at a time.

Let's say the table lists the company name, the owner, and the city at the top. I would like to have the 3 headers (labels) of the table be links which when clicked the first time will organize THIS SUBSET by that field ASCENDING. The next click on that column label would organize THIS SUBSET by that field DESCENDING.

As you can see, this is a very powerful feature for the user, a good use of everything presented to him or her, and also fairly complicated. I see very few sites with this capability (quickbase.com is an example) because of its difficulty.

IF YOU EVER NEED SOMETHING LIKE THIS YOU MIGHT SAVE THIS POST BECAUSE WHEN I GET FINISHED I'LL BE GLAD TO SHARE IT. I'm setting this up as a class or a function.

Sam Fullman
Compass Point Media

Anon

Hi again!

I thought the question had more to it than it seemed at first glance :-)

I guess the best way for you would be to find examples of sorting algorithms, such as quicksort or bubblesort in PHP, and then implement them as a class method so that two associative arrays are compared by the value found at a specific key (in your case it could be a callback function like this:

cmp_by_key_value($arr1, $arr2, $column)
{
if ($arr1[$column] == $arr2[$column]) return 0;
return ($arr1[$column] > $arr2[$column]) ? -1 : 1;
}

The sort() method could then implement the specific sorting algorithm you pick and compare items using the above function.

You cannot use native PHP sorting functions since you will need to compare arrays by specific array keys, and such function is not yet available (I guess).

This way you will be able to sort results returned from SQL with the LIMIT clause.
You can apply reverse logic by simply returning -1 instead of 1 and 1 instead of -1 from the above function.

You should also read up on different sorting algorithm since their speed depends on whether the dataset is already almost sorted or not, and on the number of elements they need to be applied to.

Hope this helps,
Stas