Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-18 : 09:59:29
|
| Dinchak Daruwala writes "1. Usually i have seen that in asp pages which show multipage output the recordset used actually holds all the records.Some of the records are skipped and the rest shown as per user convenience in batches of 10/20 records per screen.Now what really happens in case of a 10 page display and the database holds a trillian records. The record set unnecessarily holds that huge data when its supposed to display records from say 20-30 only.Is there a sql command wherein i can get the middle 20 records out of a batch of say 100 records returned by the query.Similar to the top x query command.Is there something like mid x,y where in it returns say records from 20-30 from a batch of 50 records which are fetched from the query.2. This is a easier question. Consider this sql "select * from tblusers where username<>'sqlguru' This returns records where username is not 'sqlguru' Same way "select * from tblusers" returns all records that EXIST in the table. Is there a query to return all the NON EXISTENT records in the table ?" |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-03-18 : 09:59:29
|
| Yes. We wrote an article on that: http://www.sqlteam.com/item.asp?ItemID=566 |
 |
|
|
Jay99
468 Posts |
Posted - 2002-03-18 : 10:35:04
|
quote:
...Is there a query to return all the NON EXISTENT records in the table ?...
select *from tbluserswhere 'this' = 'makes no sense' ... Jay |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-03-18 : 12:55:26
|
| If they don't exist, How could it return them?-Chad |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-03-19 : 08:23:56
|
| Dinchak Daruwala,How are you accessing the recordset in your asp page? Are you looping through the rows? One thing I have found that has made execution of pages quicker, especially with large amounts of data, is to set an array variable = to the recordset.aTable1Array = rs.getrowsIf you do this, then on the next line, you can close your recordset and connection to the database. After this is done, then you loop through the array to retrieve your values. In answering the paged recordset issue, you cansend a variable to the asp page that determines which record within the array to start with. Remember that arrays are 0 based. If you wanted to start with the 25th record, then you would start your loop through the array at aTable1Array(24) and continue for say 25 records, as follows:For i = start_value to (start_value + 25) Response.Write aTable1_Array(1(coumn you want 0 based),i(this will be the current row))NextCheck the link graz posted to optimize the number of records returned to the recordset, and use this code to optimize even further.Let me know if you have any questions.Jeremy |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-03-20 : 00:37:41
|
Re: Question 2quote:
select *from tbluserswhere 'this' = 'makes no sense' ...
will return 0 rows.If you want e.g. all the dates that are not in your table if your table is supposed to have a month's orders, you could use a temp table with all possible date values, and either doSELECT * FROM #Temp WHERE NOT EXISTS (Select date from table where date = #Temp.date) Or join the two tables on using a nonequality operator to eliminate the rows that do exist. If you are more creative, please post a more specific example of what you are trying to do.Sarah Berger MCSD |
|
|
|
|
|
|
|
|
A real teaser !