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
 General SQL Server Forums
 New to SQL Server Programming
 Select x Record based on row passed in URL

Author  Topic 

assunta
Starting Member

3 Posts

Posted - 2007-08-03 : 04:30:20
Hi all - this one has me stumped... PLEASE HELP!!!

I have a back/forward navigation link that passes a URL.startrow number (lets call it n) - based on n - I only want to select the record that is the n'th record based on a sort order (gall_order) - (SQL SERVER).

<cfquery name="gallHomePic1st" datasource="id" maxrows="1">
SELECT id
FROM gall_home
ORDER BY gall_order asc

</cfquery>

For e.g. - I want the 7th (14th - 21st etc) record based on gall_order asc.

Thanks guys - this has me stumped!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-03 : 04:39:26
Add a WHERE clause

WHERE gall_order > @n



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

assunta
Starting Member

3 Posts

Posted - 2007-08-03 : 05:01:16
Wont work - the sort_order has nothing to do with URL.startrow - this drives a thumb gallery - when you select a thumb - it pass an id through the url which in turn is retreaved by another sql query (select id from gall_home where id = URL.id). The problem is, when the user clicks 'next' to retriev the next group of thumbnails - I want the image to reflect the first thumb in this new group - hence why I nedd the '7th(14th,21st,etc)' record id. Any other ideas??? Can this be done in sql or do I need to play around with some funky coldfusion to achieve this?

Thanks
Go to Top of Page

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-08-03 : 14:45:52
Did you tried using Row_Number function? Please look into Book online for more help.

SELECT id, ROW_NUMBER() OVER (order by gall_order asc)
FROM gall_home
WHERE RowNumber = n
Go to Top of Page

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-08-03 : 14:47:48
assunta,

Search this site for "paging". You'll find several dozen answers.
Go to Top of Page
   

- Advertisement -