Selecting records from a position in a table

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting records from a position in a table

Author

Topic

Boss
Starting Member

7 Posts

Posted - 2003-09-25 : 10:10:44

Hi Everyone

I'm having a bit of bother with some SQL.I have looked in books but still do not even know if this is possible.

Basically I need to beable to look at specific portions of a table, specifically the top x amount, the middle x amount and the bottom x amount of a table.
Normally I would just write a cursor but these tables can be anything from a couple of hundred to a couple of million records and a cursor would just take too long to execute.

The top is easy i.e. select top 100 * from customer but I can find nothing for middle and bottom.

Is it possible to do this without needing a cursor, or if i must use a cursor, how do i optimize it so it does not take too long

thanks

Da Boss

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-09-25 : 10:40:43

There is an article on the site called "What Comes After Top" (or something similiar.) That will explain how to avoid a cursor in finding the subsequent groupings of X number of records. (I won't say middle because there is no actual "middle" in relational data.)

[url]http://www.sqlteam.com/item.asp?ItemID=566[/url] Here is the article I mentioned above.

Justin

"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!"

Boss
Starting Member

7 Posts

Posted - 2003-09-25 : 11:15:56

thanks for this, unfortunately the article you refer to still seems to want me to pull out all records from the table, something i wanted to avoid as the tables can be quite large.

A mate mentioned it might be possible using the sysindexes with a cursor.(I know i said I wanted to avoid cursors, but as long as it is not too slow I'm willing to entertain any idea). With this I can therefore skip all the intermediate records and only pull back the ones i want, making the cursor quick.

Does anyone know if this is possible, he said it was only an idea and was not sure?

Da Boss

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-09-25 : 11:21:12

What you are asking for is like "I want the 47th grain of salt in this salt shaker". It's not possible, unless you take all the salt grains out of the shaker and put them in order.

I'd recommend trying both the TOP and the cursor method and see which is faster. I'm not sure where your mate is going with the sysindexes thing ...

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-25 : 11:24:46

If this is one particular table, and for some reason you must do this often, just create an identity field (DO NOT make it the primary key, just add it on there).

then use that column to slice and dice to your heart's content.

Jay -- I like the salt shaker analogy ! very nice, I may have to start using that one. (i'll give you proper credit)

- Jeff

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-09-25 : 11:32:37

I think, then, the proper credit should go to Rob Volk ... but I'm too lazy to put a bibliography on all my posts ...

Jay White
{0}

Boss
Starting Member

7 Posts

Posted - 2003-09-25 : 11:34:58

Thanks
I did not think it was possible, but wanted the check to see if anyone had any ideas.And my mate said it was only an idea but wasn't sure.

Unfortunately I can't alter the tables I am querying, the reason being the tables are not owned by us, but by another supplier. I am only writing a web front-end to view the database.I can create temp tables etc but cannot alter the main tables.

I'll probably just have to use the cursor option, and they will need to wait fore their results.

Da Boss

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-25 : 11:44:42

Can you post the DDL? That might help us see what you have to work with...

[bang]Maybe there's a dattime column when the row is added[/bang]

Hope that shot in the dark didn't hurt anyone...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-25 : 12:01:12

are you talking about PAGING through the tables, then, in ASP?

it helps to be specific, you can get answers much better that way!

check this out for some ideas:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27297&SearchTerms=paging

- Jeff

Boss
Starting Member

7 Posts

Posted - 2003-09-25 : 12:11:46

Unfortunately I cannot do that as this project is supposed to be generic.

The client has many tables on many databases and all have different designs, so it is impossible to actually say "This is the DDL"

Da Boss

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-25 : 14:09:19

I have a question:

quote:
Basically I need to beable to look at specific portions of a table, specifically the top x amount, the middle x amount and the bottom x amount of a table

Why do you need to do this? Seriously, what do you do with the data in each section?

I would recommend that instead of doing SELECT TOP 100 * FROM..., you select only the primary key column(s) and the other column(s) you wish to order by (you NEED an ORDER BY for TOP to work correctly) into a temp table. That way you'll have a nice narrow table that has a minimum of data. Should you need to access specific rows, you can JOIN that temp table back to the original. You can also use the temp table in a subquery to get only specific key values; this will allow the optimizer to use the clustered index of the original table more effectively.

Jeff had a routine somewhere (that he always posts for questions like this...except now! Jeff???) that is probably perfect for this. Of course I can't find it, but I'll bet Jeff can.

Boss
Starting Member

7 Posts

Posted - 2003-09-26 : 04:20:40

ok, sorry I obviously was not clear in the first place.

The story is :-

The client has many databases all associated with different sub-clients, basically a marketing database. They want to beable to look at their data in an "presentable" format(web page instead of QA) , something that can be shown to the end clients but also something that is not too technical(mainly because they are account handlers, not programmers).

There are approx 50 different databases, all with unique structures and with only a few exceptions that want to beable to bring up any table in the database.

The two options they want are a random sample(which is not a problem) of x amount or x top,x middle,x bottom i.e. samples of newest records, oldest records and some from inbetween.

Is it possible after i have declared the cursor to tell it to skip x amount of records

e.g.In a table of 1000 recs and I want 50 t/m/b then it should just be a case of start the cursor, take the first 50 records then tell it to skip 425, take the next 50, skip the next 425 and take the last 50?

I would think that would be quick enough so as not to leave the use going to get a coffee while he waits for his answers :))

Da Boss

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-09-26 : 07:07:25

for random samples...of all the data...look at the ORDER BY NEWID()..search here for example of same...

you could select 10 random records...to then drive a 2nd query selecting the next 9 records after each of the 10....

the key here...is "next" + "after"....the use of the terms "next", "previous" and "after" implies an ORDER....and a data relationship between rows...row#1 is first because it has the "lowest value in column-x" for example...is the type of relationship we're talking about.

so you need to be able to link 'by value' the 1st record record returned with the 2nd record....ie some column(s) of the 1st record have a data relationship with column(s) on the 2nd record...ie keyvalue = keyvalue + 1...and the like.

you also mention 'newest','oldest'.....these too imply an order...and also they suggest that somewhere in your data you have (or need to have) a date/time (updated and/or created) value on each record.

i know you say your client has many databases, etc....but if you can provide a sample for one....with sample test data...then you may get some collective effort from the people here that will point you in the right direction.

As some point in the evolution of solving this problem, you WILL have to move from theory to practice....by providing the requested information here, you may advance to this stage a lot faster.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-26 : 07:24:02

time out .... suppose there are 10,000 rows in the table.

Now, I log into the web page, to browse the data.

And I want to find customer "XYZ".

I have to keep hitting NEXT ... NEXT ... NEXT and MANUALLY search through 10,000 rows to find customer XYZ ? And, depending on the sort of the data, those rows may be the the first page, 12th page, 39nd page AND the 73rd page ? because if it is not SORTED by customer, they can be all spread out.

YOu are trying to do something too generic to be really useful to anyone. I think Rob mentioned this earler, for each table, forget about paging and returning all rows: you need to determine the "key fields" in each table and allow the web user to FILTER based on those fields. It will be a totally worthless thing to say you can only view data based on some "ordering" which of course does not exist in Relational Databases as well all have told you.

instead of letting the user pick a "page" from an unordered set of data, have them pick a "Customer" or something else more meaningful. Does this make sense?

- Jeff

Boss
Starting Member

7 Posts

Posted - 2003-09-26 : 09:24:29

ok....but...Wait a minute

I have already done the specific record request stuff i.e. querying on id. This is purely so the end clients can view a sample of their own data being held by the datahouse. Because it is not the entire table the issue with page after page etc of data should not arise because it is only a sample.

I have been looking into this a bit more and what if I use absolute position with a cursor.
e.g.


declare @theamount integer, @themiddlestart integer, @theendstart integer, @totalrecs integer,@personurn varchar(100),@listid varchar(10),@currentrecno integer
select @totalrecs = count(*) from source
select @theamount = 50
select @themiddlestart = ((@totalrecs -(3 * @theamount))/2) + @theamount 
select @theendstart = @totalrecs - @theamount
DECLARE INPUT_TABLE scroll CURSOR FOR SELECT personurn,listid FROM source  

OPEN INPUT_TABLE

--READ FIRST RECORD

--top
select @currentrecno = 0
fetch absolute 1 from input_table into @personurn, @listid
WHILE @@FETCH_STATUS = 0 and @currentrecno < @theamount
begin
	insert into #matchbacksampletmb values (@personurn,@listid)		
	select @currentrecno = @currentrecno + 1
	FETCH NEXT FROM input_table INTO @personurn, @listid		
end

--middle
select @currentrecno = 0
fetch absolute @themiddlestart from input_table into @personurn, @listid
WHILE @@FETCH_STATUS = 0 and @currentrecno < @theamount
begin
	insert into #matchbacksampletmb values (@personurn,@listid)		
	select @currentrecno = @currentrecno + 1
	FETCH NEXT FROM input_table INTO @personurn, @listid		
end

--bottom
select @currentrecno = 0
fetch absolute @theendstart from input_table into @personurn, @listid
WHILE @@FETCH_STATUS = 0 and @currentrecno < @theamount
begin
	insert into #matchbacksampletmb values (@personurn,@listid)		
	select @currentrecno = @currentrecno + 1
	FETCH NEXT FROM input_table INTO @personurn, @listid		
end
close input_table
deallocate input_table

<<pls excuse the sloppyness with naming conventions, it is only so i can test the theory>>

I tested this over 2 million recs and it took less than 5 secs.

Da Boss

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-09-29 : 08:13:25

have a read.....you may decide to change tack.

http://www.sqlteam.com/item.asp?ItemID=5761

to display 150 records....you're forcing EACH user to read 2m....take Jeff's advice...filter out the stuff that is of "no interest" first.

Boss
Starting Member

7 Posts

Posted - 2003-09-29 : 12:54:00

Thanks, unfortunately It seems I have no choice in the matter.

I wish to view the sample from the top, middle and bottom of the table. The only way to do this is to use absolute position, I cannot alter the table and add an id field. The id fields already in place may not be accurate, as records could be deleted from the table thus making the middle record on a 100 record table not 50.

In this case the user wants to view whatever is in those positions so there are no records of "no interest" that I can remove with a select statement.

If you see my first post, I did want to avoid using a cursor for exactly the reason the article you posted Andrew has highlighted.i.e. it is slower, however I can see no other way.

thanks again

Da Boss

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-29 : 13:04:12

But even if you use your cursor, the results are meaningless because you don't have order by...

One day a record could be in the top, the next in the bottom.

Could you descri\be what the business requirement is?

No tech talk...

Plus 2 mill for every user?

Sounds like the NJ Turpike during rush hour...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources