[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Return a select # records in an SQL

Posted on 1998-09-26
11
Medium Priority
?
287 Views
Last Modified: 2010-03-19
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
Comment
Question by:EVision
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 1

Expert Comment

by:kdimmock
ID: 1090278
SET ROWCOUNT 10
SELECT * FROM AUTHORS
SET ROWCOUNT 0
0
 

Author Comment

by:EVision
ID: 1090279
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
 

Author Comment

by:EVision
ID: 1090280
I need to ask...

WHERE Person_ID not in ('Top 10 person ids from another SQL')
ROWCOUNT will not work.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:EVision
ID: 1090281
Is it possible to use a server-side open cursor within an IN statement?
0
 
LVL 1

Expert Comment

by:kdimmock
ID: 1090282
couldn't you create a temp table corresponding to the sub query with only 10 rows?
0
 

Author Comment

by:EVision
ID: 1090283
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
 
LVL 1

Expert Comment

by:kdimmock
ID: 1090284
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
 
LVL 1

Expert Comment

by:kdimmock
ID: 1090285
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
 

Expert Comment

by:doubas
ID: 1090286
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
 

Author Comment

by:EVision
ID: 1090287
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
 

Accepted Solution

by:
doubas earned 200 total points
ID: 1090288
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question