How can I get the bottom 50% of a select query in SQL server?

Question

In SQL server I can say:

Select top 50 percent

How can I say bottom 50 percent?

EDIT - for the sake of an interesting question, if we assume he has a table with a primary key but wants the bottom 50% ordered in ascending primary key order. What would be the most efficient way of achieving that?

Answer 1

why not just change the sort order and keep TOP. Wouldn't that accomplish the same thing?

Answer 2

    SELECT * FROM
    (
        SELECT TOP 50 PERCENT *

        FROM [Table]

        ORDER BY [Field] DESC
   )
   ORDER BY [Field] ASC

Answer 3

Your question as stated is technically meaningless because without an order by clause the order in which your data is returned is undefined (in practice most databases will return in primary key order, or if you don't have a primary key then in insertion order, but that is not guaranteed)

Assuming however you have an order by clause but just haven't included it, just would use DESC instead of ASC.

Answer 4

I dunno whether this would work:

SELECT * FROM <table> EXCEPT TOP 50 PERCENT...;

And I'm pretty sure something like this would be OK too:

SELECT * FROM <table> WHERE (ID NOT IN (SELECT TOP 50 PERCENT ...));

Just check the syntax, as I'm only beginning to learn SQL and can't form correct queries without modifying the a million times first ;)