Archive for July 25th, 2009
From Stack Overflow:
I have a basic MySQL table,
terms, comprised of an
I want to create an alphabetically sorted dictionary index (in the literal sense), that would list ten 10 terms above the selected term, and 20 below it
An example of this could be found on Urban Dictionary where on the left column you see the current term highlighted, and a number of terms above it, and some below, all sorted alphabetically.
As we all know, MySQL doesn’t support a
ROW_NUMBER()or a similar function so we end up resorting to user variables and sub-selects.
I also cannot create a view with user defined variables because MySQL doesn’t allow that.
MySQL indeed doesn’t support
ROW_NUMBER() and this function indeed can be emulated using session variables:
However, it will be an overkill in this case, since a more simple and more efficient set-based solution using
LIMIT can be used instead.
Let’s create a sample table and see how: