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

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

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 2

Another option could be:

Select * From (
    Select NTILE(2) Over (Order by Quantity Desc) as HalfNumber
    From Table1) as NtileTable
where HalfNumber = 1

You can then dynamically change the division of your data as you like.

Check this out.

Answer 3

SELECT * FROM (
        SELECT TOP 50 PERCENT *
        FROM [Table]
        ORDER BY [Field] DESC
   )
ORDER BY [Field] ASC

Does not work correct when the table contains a uneven amount of records. the one in the middle will be in both result sets.

this on the other hand works fine

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

upvote for mingos :)

downvote for user151323 :(

Answer 4

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

Answer 5

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 ;)

Answer 6

    SELECT * FROM
    (
        SELECT TOP 50 PERCENT *

        FROM [Table]

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