Sql Record number
in [Programming SQL]
|
From: Jeff on 11 Oct 2009 21:19 I have a rather large database that I am datamining. I am doing numerous SQL sorts to determine which methodolgy works best. I would like to determine the new "record position" of a particular record in the group. Currently I am doing it programtically by itering through the records till I find the particular record. This can be time consuming given the size of the database Is there a better method?
From: Plamen Ratchev on 12 Oct 2009 00:56 Take a look at the ranking functions, that should help you with ranking within groups: http://msdn.microsoft.com/en-us/library/ms189798.aspx -- Plamen Ratchev http://www.SQLStudio.com
From: Bob Barrows on 12 Oct 2009 09:11 Jeff(a)google.com wrote: > I have a rather large database that I am datamining. I am doing > numerous SQL sorts to determine which methodolgy works best. I would > like to determine the new "record position" of a particular record in > the group. Currently I am doing it programtically by itering through > the records till I find the particular record. This can be time > consuming given the size of the database > > Is there a better method? Err ... a sql statement with a WHERE clause? You do realize that "record position" depends on what sorting is being done on the records being retrieved ... in a relational database the concept of "record position" is meaningless - tables are defined as sets of unordered rows. Ordering is done when the records are retrieved from the rows. Iteration is not guaranteed to find a particular record in the same "position" twice, unless an order-by is used to force them to be in a particular order. -- HTH, Bob Barrows
From: Jeff on 12 Oct 2009 20:16 Yes an .. a sql statement with a WHERE clause On Mon, 12 Oct 2009 09:11:36 -0400, "Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote: >Jeff(a)google.com wrote: >> I have a rather large database that I am datamining. I am doing >> numerous SQL sorts to determine which methodolgy works best. I would >> like to determine the new "record position" of a particular record in >> the group. Currently I am doing it programtically by itering through >> the records till I find the particular record. This can be time >> consuming given the size of the database >> >> Is there a better method? > >Err ... a sql statement with a WHERE clause? > >You do realize that "record position" depends on what sorting is being >done on the records being retrieved ... in a relational database the >concept of "record position" is meaningless - tables are defined as sets >of unordered rows. Ordering is done when the records are retrieved from >the rows. Iteration is not guaranteed to find a particular record in the >same "position" twice, unless an order-by is used to force them to be in >a particular order.
From: Paul on 13 Oct 2009 07:19
Insert into a temporary table with the addition of an identity column. Then use this table as the basis for your query the identity columnh will identify record/row position. There are numerous examples of doing this on the web for Paging large amounts of data. <Jeff(a)google.com> wrote in message news:5d05d5dh7mehsgn45l8af672vbeufienlf(a)4ax.com... >I have a rather large database that I am datamining. I am doing > numerous SQL sorts to determine which methodolgy works best. I would > like to determine the new "record position" of a particular record in > the group. Currently I am doing it programtically by itering through > the records till I find the particular record. This can be time > consuming given the size of the database > > Is there a better method? > |