sql - Row repeating when using TOP on odd number of rows -

i'm using following queries produce 2 forms, each displaying half of items in table. selects top half:

select top 50 percent * products order products.product_id; 

and selects bottom half

select top 50 percent * products order products.product_id desc; 

the obvious problem when items odd, middle item displays on both forms. tried solving using count() function in various ways not work top. how else can this?

ignore odd number of rows issue when define first half. define second half rows not present in first half. use subquery in(), left join approach faster.

select secondhalf.*     products secondhalf     left join     (         select top 50 percent product_id         products         order product_id asc     ) firsthalf     on secondhalf.product_id = firsthalf.product_id firsthalf.product_id null; 

the subquery, firsthalf, same first query in question. in situation, need product_id subquery.

left join in main query means return rows products ... match firsthalf.product_id plus don't match. where clause filters out matches ... leaving product rows don't match subquery rows.

it might easier understand if save products different name, make new products containing few rows , experiment query. discard where clause , change first line select firsthalf.product_id, secondhalf.product_id.