I'm not sure what you've answered there. I was hoping that the windowing functions would be available as filters to allow paging like behaviour. So in the past, or rather right now, to get something similar you could create a temp table with an identity column which would act as your row numbering. I thought that ROW_NUMBER() would mean an end to that. Maybe I'm still missing the point.
"do you regard your rows in a table as logical or simply insertions with no logical order?" Both, depends how I'm feeling ;O) Ade -----Original Message----- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: 09 September 2004 11:33 To: '[EMAIL PROTECTED]' Subject: RE: [ cf-dev ] Yukon WAS: A new problem ??? Well, imagine this...ROW_NUMBER is used in dynamic evaluation of resultsets in T-SQL...think of it like CF MOD paging in SQL....it allows you amongst other things to bring back a set (or single) row based on its rank in the current resultset. Your example needs to be something like : SELECT ROW_NUMBER() OVER(ORDER BY FIELD1) AS rownum, FIELD1, FIELD2, .[...] FROM YOURTBLE ORDER BY FIELD1 Basically its gonna be a function you will or will not use, it all depends on one thing...do you regard your rows in a table as logical or simply insertions with no logical order? One N -----Original Message----- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: 09 September 2004 11:15 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Yukon WAS: A new problem ??? How can you use ROW_NUMBER()? My first thought was something like this... SELECT *, ROW_NUMBER() OVER(ORDER BY TestID) AS RowNumber FROM tblTest WHERE RowNumber BETWEEN 10 AND 20 ORDER BY RowNumber .. but you can't have the alias in the WHERE clause. Am I missing the point? Ade -----Original Message----- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: 09 September 2004 10:59 To: '[EMAIL PROTECTED]' Subject: RE: [ cf-dev ] Yukon WAS: A new problem ??? I have loved it so far...come a long way from Alpha....to be honest is the T-SQL additions / modifications which are proving to be fantastic..... ROW_NUMBER is one of those holy grail (and nemesis) of RDBMS...some people require it etc and some people think its stupid.....jeez Oracle has had these abilities for while now. The biggest addition has to be Reporting Services (now available for 2K also) and the integration of the CLR...which is huge for us. N -- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ CFDeveloper Sponsors and contributors:- *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com* *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com* To unsubscribe, e-mail: [EMAIL PROTECTED]
