Problem with ORDER BY and duplicate columns

Greetings,

I'm building a previous and next button feature with php. I'm having an
issue with a query who's results I don't understand.

This is the table;
---------------------------------------
glossary_id term
----------------------------------------
1 Example Term
2 Another Example Term
3 This should show up.
4 test term 1
5 test term 1
6 zee zee
7 uli uli
8 cat in the hat
9 just to test div overflow

My original plan was to get the columns glossary_id and term.

SELECT glossary_id, term FROM glossary WHERE glossary_id = "5";
// "5" comes from a html form

outputs,

-----------------------------------
glossary_id term
----------------------------------
5 test term 1


Then get the glossary_id of the smaller term columns limited by 1.

SELECT glossary_id FROM glossary WHERE term < "test term 1" ORDER BY
term DESC LIMIT 1;

outputs,
----------------------------------
glossary_id
---------------------------------
9

This is the table sorted by term;
----------------------------------------
glossary_id term
----------------------------------------
2 Another Example Term
8 cat in the hat
1 Example Term
9 just to test div overflow
4 test term 1
5 test term 1
3 This should show up.
7 uli uli
6 zee zee

It outputs 9 because there is a duplicate term column,
----------------------------------------
glossary_id term
----------------------------------------
4 test term 1
5 test term 1

This works in every regards except if there's a dup. It skips right
over glossary_id 4! Unfortunately, unique columns for term is not an
option.

So then I thought I'd change my tactic and try this;

SELECT glossary_id, term FROM glossary WHERE glossary_id < "5" ORDER BY
term ASC LIMIT 1;

which outputs an unexpected result (for me anyhow),
----------------------------------------
glossary_id term
----------------------------------------
2 Another Example Term


Again, this is the table sorted by term;
-----------------------------------------
glossary_id term
-----------------------------------------
2 Another Example Term
8 cat in the hat
1 Example Term
9 just to test div overflow
4 test term 1
5 test term 1
3 This should show up.
7 uli uli
6 zee zee

My question today is, why would it output this, and more importantly,
is there a better way to do my first plan without it breaking if
there's a dup?

Thank you for your attention,
Luc M. Forget

stat_holyday@hotmail.com wrote:
>
> This is the table;
> ---------------------------------------
> glossary_id term
> ----------------------------------------
> 1 Example Term
> 2 Another Example Term
> 3 This should show up.
> 4 test term 1
> 5 test term 1
> 6 zee zee
> 7 uli uli
> 8 cat in the hat
> 9 just to test div overflow
>
> SELECT glossary_id, term FROM glossary WHERE glossary_id = "5";
> // "5" comes from a html form
>
> -----------------------------------
> glossary_id term
> ----------------------------------
> 5 test term 1
>
>
> Then get the glossary_id of the smaller term columns limited by 1.

You forgot to tell us your exact definition of "the smaller term".
I guess you mean the predecessor. Read on below...

> SELECT glossary_id FROM glossary WHERE term < "test term 1" ORDER BY
> term DESC LIMIT 1;
>
> ----------------------------------
> glossary_id
> ---------------------------------
> 9

Correct!

You did expect 4 - did you?

The row with glossary_id=4 does not satisfy your query: the LESS THAN
condition in WHERE is not fulfilled.

> This is the table sorted by term;
> ----------------------------------------
> glossary_id term
> ----------------------------------------
> 2 Another Example Term
> 8 cat in the hat
> 1 Example Term
> 9 just to test div overflow
> 4 test term 1
> 5 test term 1
> 3 This should show up.
> 7 uli uli
> 6 zee zee

Nope! It's ambiguous. It could be as well

....
> 9 just to test div overflow
> 5 test term 1
> 4 test term 1
> 3 This should show up.
....

(in that case you would have accepted answer "9" - right?)

> So then I thought I'd change my tactic and try this;
>
> SELECT glossary_id, term FROM glossary WHERE glossary_id < "5" ORDER BY
> term ASC LIMIT 1;
>
> which outputs an unexpected result (for me anyhow),
> ----------------------------------------
> glossary_id term
> ----------------------------------------
> 2 Another Example Term

Again absolutely correct.

> My question today is, why would it output this, and more importantly,
> is there a better way to do my first plan without it breaking if
> there's a dup?

To define a total (that is: non-ambiguous) order you have to use
additional column(s) to resolve duplicates. I.e. you could sort

SELECT ... FROM glossary ... ORDER BY term, glossary_id

Now you have a total order and the terms "predecessor" and "successor"
are well defined. Formulating the SQL query to find the predecessor of
a given row is left as exercise to the reader ;-)


PS: maybe you want to read on total vs. partial ordering in a textbook
fascinating stuff, really ;-)

XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/