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: cate on 28 Jan 2010 13:49 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.
From: Mark D Powell on 28 Jan 2010 14:04 On Jan 28, 1:49 pm, cate <catebekens...(a)yahoo.com> 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. Look up the rownum psuedo column in your SQL manual. Basically select rownum, date_col from ( select date_code from t order by 1 ) HTH -- Mark D Powell --
From: cate on 28 Jan 2010 14:11 On Jan 28, 1:04 pm, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > On Jan 28, 1:49 pm, cate <catebekens...(a)yahoo.com> 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. > > Look up the rownum psuedo column in your SQL manual. > > Basically select rownum, date_col from ( select date_code from t order > by 1 ) > > HTH -- Mark D Powell -- OK, came up with this SELECT mypos FROM ( SELECT ROWNUM mypos, q.* FROM tOne q WHERE q.keyid1n = 201574 ORDER BY makeDate ) myrows, tOne x WHERE x.key = myrows.key AND x.flecth IS NULL; thanks
From: Mark D Powell on 28 Jan 2010 14:17 On Jan 28, 2:11 pm, cate <catebekens...(a)yahoo.com> wrote: > On Jan 28, 1:04 pm, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > > > > > On Jan 28, 1:49 pm, cate <catebekens...(a)yahoo.com> 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. > > > Look up the rownum psuedo column in your SQL manual. > > > Basically select rownum, date_col from ( select date_code from t order > > by 1 ) > > > HTH -- Mark D Powell -- > > OK, came up with this > > SELECT mypos > FROM ( SELECT ROWNUM mypos, q.* > FROM tOne q > WHERE q.keyid1n = 201574 > ORDER BY makeDate ) myrows, tOne x > WHERE x.key = myrows.key AND x.flecth IS NULL; > > thanks- Hide quoted text - > > - Show quoted text - Warning the rownum may be assigned before the sort in the SQL you posted. You should assign it to the ordered data if you want the actual relative from the first sorted row order. HTH -- Mark D Powell --
From: cate on 28 Jan 2010 14:41
On Jan 28, 1:17 pm, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > On Jan 28, 2:11 pm, cate <catebekens...(a)yahoo.com> wrote: > > > > > On Jan 28, 1:04 pm, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > > > > On Jan 28, 1:49 pm, cate <catebekens...(a)yahoo.com> 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. > > > > Look up the rownum psuedo column in your SQL manual. > > > > Basically select rownum, date_col from ( select date_code from t order > > > by 1 ) > > > > HTH -- Mark D Powell -- > > > OK, came up with this > > > SELECT mypos > > FROM ( SELECT ROWNUM mypos, q.* > > FROM tOne q > > WHERE q.keyid1n = 201574 > > ORDER BY makeDate ) myrows, tOne x > > WHERE x.key = myrows.key AND x.flecth IS NULL; > > > thanks- Hide quoted text - > > > - Show quoted text - > > Warning the rownum may be assigned before the sort in the SQL you > posted. You should assign it to the ordered data if you want the > actual relative from the first sorted row order. > > HTH -- Mark D Powell -- You have me. How would I secure the rownumbers? I'll reveiw your first suggestion. Shoot, I thought I was there!. Thanks. |