If you are worried about the order you will need to nest a select statement
e.g.

select a.* from (select top 50 percent * from vausnzcities where est_countryId = 169
order by city desc) a
order by a.city

I do not recommend this way of doing.

Using TOP with an static number, instead of a percent, is not really elegant since you first need to check how many records there is with COUNT. In your first query you divide that number by two (ie: 15/2 = 7) to get the TOP number. Then in your second query you substract that number to the count (ie: 15 - 15/2 = 8) to get the second TOP number.


You need to replace the "10" with "50 percent". In that case the solution proposed, on a table with an odd number of records, would return the "middle" record twice.

For a table with 5 records, the first query will return 1,2 and 3. The second query will return 5,4,3.

It's also not very useful to have your records sorted in two different ways. Like Andrew said, you can use a nested statement to correct this, but if you are to use a nested statement, you should instead do the following.

So I think we are better off with a more simple and elegant solution that's guaranteed to work everytime, doesn't mess with the order and doesn't require you to use COUNT to perform some mathematical operations.


What we can do is use a subquery in the WHERE clause of the second query to filter the first half :

First query (select the first 50 percent) :
SELECT TOP 50 percent FirstName, LastName, BirthDate
FROM Persons
ORDER BY LastName, FirstName


Second query (select everything BUT the first 50 percent) :
SELECT TOP 50 percent FirstName, LastName, BirthDate
FROM Persons
WHERE id NOT IN (SELECT TOP 50 percent id FROM Persons)
ORDER BY LastName, FirstName


As simple as that. You can be sure this will always work, no matter how much records you have or whatever.

For the subquery, don't forget to use the same ordering clause as the first query!!

SELECT TOP 50 percent FirstName, LastName, BirthDate
FROM Persons
WHERE id NOT IN (SELECT TOP 50 percent id FROM Persons ORDER BY LastName, FirstName)
ORDER BY LastName, FirstName