How To Query the Top N or N% Records in Visual Basic.Net

[Guest]

I have a VB.Net application that reads from a SQL database (SQL Server 7.0).
How do I get the top 50% or bottom 50% of records from a certain table for my
dataset? I keep getting an exception (incorrect syntax) when using Top 50
Percent in my SQL statement (sSQL = "SELECT TOP 50 PERCENT tblInventory.*
FROM tblInventory "). Does vb.net not recognize this statement?

TIA for any help.
(e-mail address removed)

 

[Ken Dopierala Jr.]

Hi John,

Your code looks fine. VB.Net doesn't have anything to do with this, it
isn't going to check your query for you, but rather just pass it along.
What you might want to do is set a break point after you assign sSQL, get
the value, and paste the value into something like Query Analyzer and see if
you receive an error. Good luck! Ken.

--
Ken Dopierala Jr.
For great ASP.Net web hosting try:
http://www.webhost4life.com/default.asp?refid=Spinlight
If you sign up under me and need help, email me.


At the top of my stored procedure.

 

[Will Gillen]

John --

As far as I know SQL Server doesn't support "TOP 50 PERCENT", but does
support "TOP 50" or any other integer of records to take from the top.
So, what you could do is this:

First select the # records in the table like this:
SQL = "select count(*) from table1"

Now, if you want the top 50% do this:
take the count from the abot SQL and multiply by your percentage (in this
case .50)

Now take that resulting number as your "top" number to select from the Table
and perform the following SQL

SQL = "select TOP " & myTop & " from table1;"

Hope this helps....

-- Will G.

 

[Will Gillen]

I stand corrected:
SQLServer does support top x percent:

Try this:

select top 50 percent * from tblInventory ;

Not sure about other DB...

-- Will Gillen

"Will Gillen" wrote in message

 

[Guest]

The problem you are trying to solve requires multiple steps.

When you query the database, the records are sorted by one or more fields.
If you do not have an ORDER BY clause in your statement, SQL sorts the
records for you.

By way of simple example:

Say you have an ID field for 100 records and that field provided an
incremental value for each record. You would write a query such as SELECT
ID, InventoryItemA, InventoryItemB FROM tblInventory ORDER BY ID. You would
go to the last record and get the ID, calculate half its value, and hold it
in a variable.

You would requery with the same statement, but include a WHERE clause before
ORDER BY: WHERE ID <= calcValue.

You should be able to get your DB Admin to add an incremental field if there
is not one already.

Hope that helps.

www.charlesfarriersoftware.com