SQL Union with ORDER BY

[handaman]

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

[Gert-Jan Strik]

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

[Hugo Kornelis]

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)

[boblotz2001]

select * from (Select Top 5 MagazineID, MagazineName from Magazine
Order by MagazineID) AS a order by MagazineID desc