September 1, 2007 at 3:27 pm
Sorry to post a question about a query... I'm a developer, that's my excuse and I'm sticking to it.
I am building a picture web site that has a new theme every week. I am populating a drop down list with the names of the themes.
The table is a simple table with two columns: idTheme, and ThemeName.
What I need to do is construct the query so that it returns the row with the highest index first, followed by the rest sorted in alphabetical ordering. That will keep the most recent theme at the top of the list while keeping the rest sorted alphabetically.
I tried a union, but that doesn't like to operate on two ordered queries.
September 2, 2007 at 4:53 am
With union you can have a single sortby which covers both queries in the union. Add another column to both queries and set the value to 1 for the rows you want to appear at the top and 2 for the rest.
SELECT 1 AS Sequence, idTheme, ThemeName WHERE idTheme IN (SELECT TOP 1 idTheme FROM Theme ORDER BY <<highestindex>>
UNION
SELECT 2 AS Sequence, idTheme, ThemeName ORDER BY Sequence, ThemeName WHERE idtheme NOT IN (SELECT TOP 1 idTheme FROM Theme ORDER BY <<highestindex>>
ORDER BY Sequence, ThemeName
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply