previous/next in sql
alright, i'm trying to create a "previous/next" statement so that my query only grabs 6 records at a time. but first off, i have an order by statement that reads: order by datediff(dd,expirationDate,getdate())/abs(datediff(dd,expirationDate,getdate())),businessName so i can't just grab where the id field is greater than the last one outputted. then i tried creating it in a cursor in sql, but its a dynamic query, and i couldn't have a sql "if" statement within the "where" clause, to check if a value was 0 or not something like: where 0=0 if (@stateId0) and stateId=@stateId i might be missing something, or maybe someone has a better idea, but what i'd really like to do (if it can be done) is grab 6 records, and within sql, be able to specify the row number of the outputted queryso then i can put where (row number) 6 for the next set of records. Brandon Paolin Programmer AVERICOM Communications 856-874-1133 ext. 321 1931 Olney Avenue * Suite 600 Cherry Hill, NJ 08003 http://www.avericom.com ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: previous/next in sql
alright, i'm trying to create a "previous/next" statement so that my query only grabs 6 records at a time. How about using a cursor and submitting the ID of the last record processed as a parameter to the stored procedure? Then just loop through the results until you hit the ID passed to the procedure and return the next 6 rows. Russell Conway HallofSports.com, Inc. 351 West 22nd Street New York, NY 10011 P (646) 638-2500 F (646) 638-3444 http://www.hallofsports.com .. . . where the legends live on ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: previous/next in sql
i can't use a cursor cause its a dynamic query, and i couldn't have a sql "if" statement within the "where" clause, to check if a value was 0 or not something like: where 0=0 if (@stateId0) and stateId=@stateId - Original Message - From: "Russ Conway" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Wednesday, April 04, 2001 3:38 PM Subject: RE: previous/next in sql alright, i'm trying to create a "previous/next" statement so that my query only grabs 6 records at a time. How about using a cursor and submitting the ID of the last record processed as a parameter to the stored procedure? Then just loop through the results until you hit the ID passed to the procedure and return the next 6 rows. Russell Conway HallofSports.com, Inc. 351 West 22nd Street New York, NY 10011 P (646) 638-2500 F (646) 638-3444 http://www.hallofsports.com .. . . where the legends live on ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
