• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Return a select # records in an SQL

Is there a way to return the first x number of records for an SQL using MS SQL 6.5?  For example, in MS Access, I can request 'select Top 10 from Authors'.  
0
EVision
Asked:
EVision
  • 5
  • 4
  • 2
1 Solution
 
kdimmockCommented:
SET ROWCOUNT 10
SELECT * FROM AUTHORS
SET ROWCOUNT 0
0
 
EVisionAuthor Commented:
You are correct, but I did not give enough information.  I'm writing a TSQL statement and need to request

WHERE Person_ID not in ('Top 10 people ids from another SQL')

.therefore ROWCOUNT does not work.
0
 
EVisionAuthor Commented:
I need to ask...

WHERE Person_ID not in ('Top 10 person ids from another SQL')
ROWCOUNT will not work.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
EVisionAuthor Commented:
Is it possible to use a server-side open cursor within an IN statement?
0
 
kdimmockCommented:
couldn't you create a temp table corresponding to the sub query with only 10 rows?
0
 
EVisionAuthor Commented:
Possible, but inefficient.  It's for a bid agent on auction-type site.  For each insert in BIDS, I need to see if anyone has a MAX_ALLOWED_BID greater than the current lowest winning bid and not already a winner.  I'm trying to determine if the person is already a winner (TOP n Persons).  It's possible we may have 100 inserts a minute.  Too many times a temp table is created & deleted.  Any ideas???
0
 
kdimmockCommented:
Now I'm losing the plot!  Show me a cut down (but now pseudo) version of the SQL as you'd have written in access
0
 
kdimmockCommented:
typo:
Now I'm losing the plot!  Show me a cut down (but NOT pseudo) version of the SQL as you'd have written in access

0
 
doubasCommented:
in english, how would you define a winner?  eg, does "the top N persons" mean the same thing as "the N highest bidders"?  please give the names of the pertinent tables/columns.
0
 
EVisionAuthor Commented:
There is a table Bids that has Auction_Code, Bidding_Member_Code and Price_Per_Unit.  I want to know the N highest bids for Auction_Code X.
0
 
doubasCommented:
will something like this work?

.
where person_id not in(select bidding_member_code
                         from bids a
                        where a.auction_code = X
                          and N >= (select count(*)
                                      from bids b
                                     where b.auction_code = X
                                       and b.price_per_unit >= a.price_per_unit)
.


0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now