Select Top Percent vs. Select Bottom Percent 1

[RSfromCO]

I have a table that currently has 29 rows.

I have a report that is trying to list the contents of the table in two columns. I want them to come out with equal number of rows in each, with the first column having one more item if there are an odd number of items. I am trying to develop a method that will work if the table has anywhere from 1 to 200 rows.

I tried this....
To select the records for column A:
SELECT TOP 50 Percent <fields> Order By DisplayOrder;

To select the records for column B:
SELECT TOP 50 Percent <fields> Order By DisplayOrder DESC;

The problem is I am getting (in this case) the 15th row included in both queries.

I tried lowering the percent in the second query to 49, but I still get 15 records returned (the last 15 records).

Is there a good way to get the first half of a table, and the last half of a table, and be sure not to include the middle row in both result sets, and be sure not to leave out a row?

 

[AlexCuse]

For column B you might try something like this:

SELECT <fields> from <table> where DisplayOrder not in 

(SELECT TOP 50 Percent <fields> from <table> Order By DisplayOrder)

Order By DisplayOrder;

not sure if you really wanted to display in descending order, seems like that was just done to return bottom 50 percent.

Hope this helps,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[RSfromCO]

Good idea. Thanks, that works like a charm.

 

[gmmastros]

You could use the RowCount property to do this.

Ex:

Declare @Rows Int
Declare @TopRows Int
Declare @BottomRows Int

Select @Rows = Count(*) From TableName

If @Rows % 2 = 1
  Begin	
    Set @TopRows = @Rows / 2
    Set @BottomRows = @TopRows + 1
  End
Else
  Begin
    Set @TopRows = @Rows / 2
    Set @BottomRows = @TopRows
  End	

Set RowCount @TopRows

Select * From TableName Order By DisplayOrder

Set RowCount @BottomRows

Select * From TableNameOrder By DisplayOrderDESC

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

George, make sure that the last line in that code is

Set RowCount 0

also in SQL server 2005 you can use TOP RowCount (variable), that way you don't have to set any rowcounts

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Good point Denis. Thanks for pointing that out.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom