December 2, 2010 at 1:04 pm
I have about 15000 records that a client needs to compare against each other. New records are coming in all the time. I don't want to have to load up all the records for them to look at because it becomes very intensive. Here's a bit more of a summary.
Record A is entered in the system as unreviewed - first name John, last name Smith
I want to retrieve the john smith record, as well as the first 10 records before it and 10 records after it, within the order of lastname, firstname. So basically I want to pull the 10 records that would show up before and after this record if I were to select all records based by lastname, firstname.
If this is confusing, please post questions so I can clarify.
December 2, 2010 at 2:10 pm
Paging functions in queries can be a bit of a problem.
Are you using SQL 2000, as per the forum you posted in, or is there any chance you're using 2005/2008? (It's easier in those.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 2, 2010 at 2:15 pm
I thought it would be easier in those. Right now it's in 2000, there is the possibility of me upgrading to 2005, but if I can get a solution in 2000 that would be more useful to me at the present time.
December 2, 2010 at 2:59 pm
There's no quick and clean way.
One method is to do a select into a temp table for just the sorted columns with an IDENTITY() field, to find your # of rows to the target record(s).
Then, you do a select top (#row + 10) INTO #tmp ORDER BY columns asc.
Then, you reverse that select with another top 20 FROM #tmp ORDER BY columns desc.
Pagination in 2k is always painful. There's a few methods out there and they all end up dependent on your data volume. Just google up sql 2000 pagination stored procedure as a starting point.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 2, 2010 at 5:00 pm
Something like this:
SELECT TOP 10 * FROM Table
WHERE Name < @Name
ORDER BY Name DESC
UNION ALL
SELECT TOP 10 * FROM Table
WHERE Name > @Name
ORDER BY Name
You need a little bit more sophisticated script for combination of Firstname + LastName.
If you have trouble figuring it out don't hesitate to ask.
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply