Hi all,

Given the query

SELECT SomeNumber, Name
FROM SomeTable
WHERE SomeNumber IN (5, 4, 3, 2, 1, 81, 80, 79, 78, 77)

I want the records to be returned in the same order that they are
specified in the WHERE..IN clause.

Is there a way to do this?

As it stands, they are coming in ascending order,
1,2,3,4,5,77,78,79,80,81. Descending is no good because that will be
81,80,79,78,77,5,4,3,2,1.





Simon

On Wed, 13 Feb 2008 14:54:10 -0800 (PST), SimonH wrote:
> Hi all,
>
> Given the query
>
> SELECT SomeNumber, Name
> FROM SomeTable
> WHERE SomeNumber IN (5, 4, 3, 2, 1, 81, 80, 79, 78, 77)
>
> I want the records to be returned in the same order that they are
> specified in the WHERE..IN clause.
>
> Is there a way to do this?

Not as a general SQL case. You may be able to work something up with
FIND_IN_SET().

--
This was, apparently, beyond her ken. So far beyond her ken that she was
well into barbie territory.
-- J. D. Baldwin

>Given the query
>
>SELECT SomeNumber, Name
>FROM SomeTable
>WHERE SomeNumber IN (5, 4, 3, 2, 1, 81, 80, 79, 78, 77)
>
>I want the records to be returned in the same order that they are
>specified in the WHERE..IN clause.

You specify ordering with an ORDER BY clause, not in a WHERE clause.
The ordering of the numbers in the above IN operation does not imply
ordering of the results.

>Is there a way to do this?
>
>As it stands, they are coming in ascending order,
>1,2,3,4,5,77,78,79,80,81. Descending is no good because that will be
>81,80,79,78,77,5,4,3,2,1.

Use ORDER BY. I've seen some people use find_in_set() to do this:

ORDER BY find_in_set(SomeNumber, '5,4,3,2,1,81,80,79,78,77')

which for relatively short lists (e.g. under a hundred) is a good
solution.

Read the documentation and determine what happens if the number
isn't in the list at all.

It is also possible to use if() or CASE to come up with a translation
of value to ordering. This tends to look very, very ugly unless there's
some kind of pattern to the data.

Or, if it's a very complicated list (say, a few million elements),
put it in a two-column table (value and ordering for that value),
LEFT JOIN to that table, and ORDER BY the ordering value in that
table.

On Feb 14, 12:39 am, gordonb.jr...@burditt.org (Gordon Burditt) wrote:

> You specify ordering with an ORDER BY clause, not in a WHERE clause.
> The ordering of the numbers in the above IN operation does not imply
> ordering of the results.

I know but I want to /preserve/ that ordering in the results. A bare
ORDER BY clause, whether ascending or descending, won't do the job but
I'll might have a look at find_in_set().

> It is also possible to use if() or CASE to come up with a translation
> of value to ordering. This tends to look very, very ugly unless there's
> some kind of pattern to the data.

I have to maintain a particularly nasty MAX CASE for a pivot query at
work. Ugly is not the word

> Or, if it's a very complicated list (say, a few million elements),
> put it in a two-column table (value and ordering for that value),
> LEFT JOIN to that table, and ORDER BY the ordering value in that
> table.

All interesting stuff. If I tell you exactly what I am trying to do,
you might be able to point me at a much simpler solution.

My 81 records are chapters from the Tao Te Ching so there will always
be that number (okay, perhaps 82 depending on the translation). It's
not a huge dataset, no, but it will serve a simple web app. and should
be as responsive as possible.

The query I am writing here will be the engine behind an RSS feed
providing daily chapters. The feed will have 15 items in it, with the
first (top) item being "today's" chapter.

So if today's chapter is, say, 20, the RSS feed will have chapters 20
to 5 in descending order. Obviously, though, if today's chapter is 5,
I will either have to truncate the feed to 5 items (I don't want to do
this) or wrap around to the other end... 5, 4, 3, 2, 1, 81, 80, 79,
etc. So it's really a circular array.

Most of the time, where 15 < chapter < 81, descending order will work
so I could detect the wrap-around, use two queries and join the result
arrays in php but I thought there might be a way to do it in one.



Simon

On Feb 14, 11:22 am, SimonH <simon.huds...@gmail.com> wrote:

> Most of the time, where 15 < chapter < 81, descending order will work
> so I could detect the wrap-around, use two queries and join the result
> arrays in php but I thought there might be a way to do it in one.

I could select all 81 records in descending order and just output the
"window" of 15 I require, again handling the "wrap" in php. I don't
like that either.

On Feb 13, 11:39 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:

> Not as a general SQL case. You may be able to work something up with
> FIND_IN_SET().

Thanks Peter, I'll look into it.

SimonH schreef:
> On Feb 14, 11:22 am, SimonH <simon.huds...@gmail.com> wrote:
>
>> Most of the time, where 15 < chapter < 81, descending order will work
>> so I could detect the wrap-around, use two queries and join the result
>> arrays in php but I thought there might be a way to do it in one.
>
> I could select all 81 records in descending order and just output the
> "window" of 15 I require, again handling the "wrap" in php. I don't
> like that either.


select chapter from book
order by if (chapter > 15, chapter, chapter+100) desc

--
Luuk

On Feb 14, 12:29 pm, Luuk <L...@invalid.lan> wrote:

> select chapter from book
> order by if (chapter > 15, chapter, chapter+100) desc

That's beautiful.

A slight variation:

Let's say today is chapter 5:-

SELECT *
FROM `TaoChapters`
order by if (ChapterNo > 5, ChapterNo, ChapterNo + 81) desc
limit 0,15;

the Limit keyword sizes my "window" and the IF effectively positions
it.

Very nice. Thank you, Luuk.



Simon