470,618 Members | 1,727 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,618 developers. It's quick & easy.

SQL Union with ORDER BY

I am having problems retrieving some data from a table,

Tablename = "Magazine"
Columns
MagazineID MagazineName
1
2 Times
3 People
4 National Geographic
5 US
6 Sports Illustrated
I am trying to bind a dropdownlist in .net with the 4 newest magazines
and the empty magazine.

SELECT TOP 1 MagazineID, MagazineName from Magazine
UNION
SELECT TOP 4 MagazineID, MagazineName from Magazine
Order by MagazineName;

The first part returns
1,''
The second part returns
1,''
2,'Times'
3,'People'
4,'National Geographic'

Given the above data, how can I get the sql results to display
5 'US'
4 'National Geographic'
3 'People'
2 'Times'
1 ''
Essentially i need to do the following

Select Top 1 MagazineID, MagazineName from Magazine Order by MagazineID

and add to it

Select Top 4 MagazineID, MagazineName from Magazine Order by MagazineID
desc

Jul 23 '05 #1
3 15126
Hi Handaman,

The query

SELECT TOP 1 MagazineID, MagazineName from Magazine

does not always return

1,''

Since you did not specify an ORDER BY clause, this statement could
return any 1 row of the table. If you want the empty magazine, you could
specify

SELECT MagazineID, MagazineName from Magazine WHERE MagazineName=''

The same holds true for your "SELECT TOP 4" statement. It will select 4
arbitrary rows from the table. What is your definition of "the 4 newest
magazines"? If a higher MagazineID means a newer magazine, then you
could write

SELECT MagazineID, MagazineName from Magazine WHERE MagazineName=''
UNION ALL
SELECT MagazineID, MagazineName FROM (
SELECT TOP 4 MagazineID, MagazineName
FROM Magazine
ORDER BY MagazineID DESC
) AS T1
ORDER BY MagazineName

Hope this helps,
Gert-Jan
handaman wrote:

I am having problems retrieving some data from a table,

Tablename = "Magazine"
Columns
MagazineID MagazineName
1
2 Times
3 People
4 National Geographic
5 US
6 Sports Illustrated

I am trying to bind a dropdownlist in .net with the 4 newest magazines
and the empty magazine.

SELECT TOP 1 MagazineID, MagazineName from Magazine
UNION
SELECT TOP 4 MagazineID, MagazineName from Magazine
Order by MagazineName;

The first part returns
1,''
The second part returns
1,''
2,'Times'
3,'People'
4,'National Geographic'

Given the above data, how can I get the sql results to display
5 'US'
4 'National Geographic'
3 'People'
2 'Times'
1 ''

Essentially i need to do the following

Select Top 1 MagazineID, MagazineName from Magazine Order by MagazineID

and add to it

Select Top 4 MagazineID, MagazineName from Magazine Order by MagazineID
desc

Jul 23 '05 #2
On 30 Mar 2005 11:29:19 -0800, handaman wrote:

(snip)
Essentially i need to do the following

Select Top 1 MagazineID, MagazineName from Magazine Order by MagazineID

and add to it

Select Top 4 MagazineID, MagazineName from Magazine Order by MagazineID
desc


Hi handaman,

In a UNION query, you can only have one ORDER BY clause, at the end, and
it will apply to the whole result, not to one of the UNION'ed SELECT
statements.

One possible workaround is to use derived tables, something like this:

SELECT ....
FROM (SELECT TOP 1 ...
FROM ...
ORDER BY ...) AS x
UNION ALL
SELECT ....
FROM (SELECT TOP 4 ...
FROM ...
ORDER BY ... DESC) AS y

However, in your case you could also make creative use of the CASE
expression in the ORDER BY clause:

SELECT TOP 5 MagazineID, MagazineName
FROM Magazine
ORDER BY CASE WHEN MagazineName = '' THEN 1 ELSE 2 END,
MagazineName DESC
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #3
select * from (Select Top 5 MagazineID, MagazineName from Magazine
Order by MagazineID) AS a order by MagazineID desc

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Blake Caraway | last post: by
1 post views Thread by Tom Schindl | last post: by
5 posts views Thread by NAJH | last post: by
2 posts views Thread by S. van Beek | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.