find position of row in set of rows
in [Oracle]
|
Prev: performance comparision
Next: adding a constraint to child table that references a column of theparent table that is not part of the primary key
From: Tim X on 28 Jan 2010 18:01 cate <catebekensail(a)yahoo.com> writes: > I have a table ordered by date. When I find a specific record in this > set, I want to know what its position is in this ordered list. > > I could get the date from the record found and count dates above or > below, but is there a better way? > > Thank you. > the rownum pseudo column? Tim -- tcross (at) rapttech dot com dot au
From: Mladen Gogala on 28 Jan 2010 22:00 On Thu, 28 Jan 2010 10:49:56 -0800, cate wrote: > I have a table ordered by date. When I find a specific record in this > set, I want to know what its position is in this ordered list. > > I could get the date from the record found and count dates above or > below, but is there a better way? > > Thank you. Wrong paradigm. Ordered lists are from another story. Relational databases are modeled after naive set theory and deal with subsets. Relational databases do not deal with Abelian groups, Lie algebras, vector spaces or ordered lists, they deal with subsets. Subsets are, generally speaking not ordered, although any set can be well ordered, provided we accept so called Zermelo's axiom of choice, but that's no longer the naive set theory. If you need a database to return you an ordered list, you are having an application design issue. -- http://mgogala.freehostia.com
From: Galen Boyer on 28 Jan 2010 23:46 Mladen Gogala <gogala.mladen(a)gmail.com> writes: > If you need a database to return you an ordered list, you are having > an application design issue. I thought you just added an "order the naive set" clause. :-) -- Galen Boyer --- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: Robert Klemme on 29 Jan 2010 12:01 On 29.01.2010 04:00, Mladen Gogala wrote: > On Thu, 28 Jan 2010 10:49:56 -0800, cate wrote: > >> I have a table ordered by date. When I find a specific record in this >> set, I want to know what its position is in this ordered list. >> >> I could get the date from the record found and count dates above or >> below, but is there a better way? >> >> Thank you. > > Wrong paradigm. Ordered lists are from another story. Relational > databases are modeled after naive set theory and deal with subsets. > Relational databases do not deal with Abelian groups, Lie algebras, > vector spaces or ordered lists, they deal with subsets. Subsets are, > generally speaking not ordered, although any set can be well ordered, > provided we accept so called Zermelo's axiom of choice, but that's no > longer the naive set theory. If you need a database to return you an > ordered list, you are having an application design issue. I don't fully agree: certainly relational DB and SQL started out at relational algebra, but there are significant features (even in standard SQL) that are not really covered by set theory (just to name ORDER BY and the Oracle specific CONNECT BY). I do agree if OP wants to remember the position for some later access. That's not a good idea. But it can be reasonable to have the DB return positional numbers so you can easily print them in some form of UI or report. Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
From: Mladen Gogala on 29 Jan 2010 14:58
On Fri, 29 Jan 2010 18:01:18 +0100, Robert Klemme wrote: > I don't fully agree: certainly relational DB and SQL started out at > relational algebra, but there are significant features (even in standard > SQL) that are not really covered by set theory (just to name ORDER BY > and the Oracle specific CONNECT BY). That is because of the infidels who have polluted our, otherwise pure, mathematical theory. That's nothing that a good jihad couldn't sort out. Kantor, Zermelo and Goedel must be turning in their graves. -- http://mgogala.byethost5.com |