I have a question about rownum which is not mentioned.
Lets say that I want to extract a 3 records out of a result where the middle one is the query. For example: I have records: ID Value 1 Hello 2 Hi 3 Wow 4 Shrek 5 Shus 6 What?
And I need a query that by receiving ID, it will extract the record with the id and the previous one to that and the next one. For example for selecting ID=4, it will return records: 3,4,5.
> I have a question about rownum which is not mentioned.
> Lets say that I want to extract a 3 records out of a result where the > middle one is the query. > For example: > I have records: > ID Value > 1 Hello > 2 Hi > 3 Wow > 4 Shrek > 5 Shus > 6 What?
> And I need a query that by receiving ID, it will extract the record > with the id and the previous one to that and the next one. > For example for selecting ID=4, it will return records: 3,4,5.
> > I have a question about rownum which is not mentioned.
> > Lets say that I want to extract a 3 records out of a result where the > > middle one is the query. > > For example: > > I have records: > > ID Value > > 1 Hello > > 2 Hi > > 3 Wow > > 4 Shrek > > 5 Shus > > 6 What?
> > And I need a query that by receiving ID, it will extract the record > > with the id and the previous one to that and the next one. > > For example for selecting ID=4, it will return records: 3,4,5.
> > Is there a way to do it in Oracle?
> where id between <yourval> -1 and <yourval> +1 ?
> Shakespeare- -
> - -
Well, But the ids are not seqeuntial - it can verify from each record.. For exampe: ID Value 225 Hello 228 Hi 258 Wow 240 Shrek 259 Shus 230 What?
>> > I have a question about rownum which is not mentioned.
>> > Lets say that I want to extract a 3 records out of a result where the >> > middle one is the query. >> > For example: >> > I have records: >> > ID Value >> > 1 Hello >> > 2 Hi >> > 3 Wow >> > 4 Shrek >> > 5 Shus >> > 6 What?
>> > And I need a query that by receiving ID, it will extract the record >> > with the id and the previous one to that and the next one. >> > For example for selecting ID=4, it will return records: 3,4,5.
>> > Is there a way to do it in Oracle?
>> where id between <yourval> -1 and <yourval> +1 ?
>> Shakespeare- -
>> - -
> Well, > But the ids are not seqeuntial - it can verify from each record.. > For exampe: > ID Value > 225 Hello > 228 Hi > 258 Wow > 240 Shrek > 259 Shus > 230 What?
I was already affraid of that.... would be to simple, right? But what is determining your sort order then? ROWID? Be aware that rownum should be meaningless....
>>> > I have a question about rownum which is not mentioned.
>>> > Lets say that I want to extract a 3 records out of a result where the >>> > middle one is the query. >>> > For example: >>> > I have records: >>> > ID Value >>> > 1 Hello >>> > 2 Hi >>> > 3 Wow >>> > 4 Shrek >>> > 5 Shus >>> > 6 What?
>>> > And I need a query that by receiving ID, it will extract the record >>> > with the id and the previous one to that and the next one. >>> > For example for selecting ID=4, it will return records: 3,4,5.
>>> > Is there a way to do it in Oracle?
>>> where id between <yourval> -1 and <yourval> +1 ?
>>> Shakespeare- -
>>> - -
>> Well, >> But the ids are not seqeuntial - it can verify from each record.. >> For exampe: >> ID Value >> 225 Hello >> 228 Hi >> 258 Wow >> 240 Shrek >> 259 Shus >> 230 What?
> I was already affraid of that.... would be to simple, right? But what is > determining your sort order then? ROWID? Be aware that rownum should be > meaningless....
> Well, > But the ids are not seqeuntial - it can verify from each record.. > For exampe: > ID Value > 225 Hello > 228 Hi > 258 Wow > 240 Shrek > 259 Shus > 230 What?
I think this should be possible using the LAG() function. Check out the section about analytical functions in the manual.
On Aug 21, 9:52 am, odelya <be.spec...@gmail.com> wrote:
> Tha sort is not by the ID - it's by another parameter in the table > (date..)
And how does the ID 'sequence' relate to the date by which these records are sorted? If it's anything like what you posted you'll have difficulty in returning a record plus the records immediately prior and immediately following the selected data as your IDs aren't in any discernable order -- the 'prior' record may have an ID greater than the 'reference' record, and the 'next' record may have an ID greater than the 'reference' but less than the ID for the 'prior'. We need better data, and a better definition of your 'problem' before we can proceed further. What you've provided so far is an over-simplified case which doesn't represent reality.
> Tha sort is not by the ID - it's by another parameter in the table > (date..)
Then your query should
1 - select the right record by id, and it's date
2 - select the record from your table with maximum date where date < date of first record
Something like select * from <your table> t1 where date_column = ( select max(date_column) from <your table> t2 and t2.date_column < date_column_of_found_record_in_first_query )
-- I think the double select from the same table could be avoided by a group by , order by and rownum=1
3 - select the record from your table with minimum date where date > date of first record - Change max for min and < for >
assuming all dates are different or in case they are the same you don't care which record is there. (< and > should then be <= and => ).
You could do this with a union of these queries, a standard, min and max query.
But maybe there are some functions for this....
One different trick to rule out one of the union tables is to find your record and join it with a select over your table order by date of first record - date of queried record and find the smallest negative and positive differences.... but that would be too complex I guess..
OR: look on internet and search for Nearest Neighbor Algorithms
OR (Last option): wait for experts like Michel Cadot to answer your question. He's an expert in queries like these (and others!) I'm sure there must be more efficicient options for this problem.
> odelya wrote on 21.08.2007 16:38: >> Well, >> But the ids are not seqeuntial - it can verify from each record.. >> For exampe: >> ID Value >> 225 Hello >> 228 Hi >> 258 Wow >> 240 Shrek >> 259 Shus >> 230 What?
> I think this should be possible using the LAG() function. Check out the > section about analytical functions in the manual.
> Thomas
Right, that's what I was looking for but could not find anymore!
> I have a question about rownum which is not mentioned.
> Lets say that I want to extract a 3 records out of a result where the > middle one is the query. > For example: > I have records: > ID Value > 1 Hello > 2 Hi > 3 Wow > 4 Shrek > 5 Shus > 6 What?
> And I need a query that by receiving ID, it will extract the record > with the id and the previous one to that and the next one. > For example for selecting ID=4, it will return records: 3,4,5.
>> I have a question about rownum which is not mentioned.
>> Lets say that I want to extract a 3 records out of a result where the >> middle one is the query. >> For example: >> I have records: >> ID Value >> 1 Hello >> 2 Hi >> 3 Wow >> 4 Shrek >> 5 Shus >> 6 What?
>> And I need a query that by receiving ID, it will extract the record >> with the id and the previous one to that and the next one. >> For example for selecting ID=4, it will return records: 3,4,5.
> I have a question about rownum which is not mentioned.
> Lets say that I want to extract a 3 records out of a result where the > middle one is the query. > For example: > I have records: > ID Value > 1 Hello > 2 Hi > 3 Wow > 4 Shrek > 5 Shus > 6 What?
> And I need a query that by receiving ID, it will extract the record > with the id and the previous one to that and the next one. > For example for selecting ID=4, it will return records: 3,4,5.
> Is there a way to do it in Oracle?
Note: LAG and LEAD will collapse the result into a single row. If that is not desired, you will need a different approach: The set up: CREATE TABLE T1 ( C1 NUMBER(12), C2 VARCHAR2(20));
INSERT INTO T1 VALUES (1,'Hello'); INSERT INTO T1 VALUES (2,'Hi'); INSERT INTO T1 VALUES (3,'Wow'); INSERT INTO T1 VALUES (4,'Shrek'); INSERT INTO T1 VALUES (5,'Shus'); INSERT INTO T1 VALUES (6,'What?');
A simple query using the ROW_NUMBER analytical function:
SELECT C1, C2, ROW_NUMBER() OVER (ORDER BY C1) RN FROM T1;
Sliding the above into an inline view to retrieve only those on either side of RN=4: SELECT T.C1, T.C2 FROM (SELECT C1, C2, ROW_NUMBER() OVER (ORDER BY C1) RN FROM T1) T WHERE T.RN BETWEEN (4 -1) AND (4 +1);
C1 C2 ---------- ------ 3 Wow 4 Shrek 5 Shus
But, the above is not exactly what you need, unless C1 (your ID column) always starts at 1 and incements by 1. Essentially listing the inline view twice with a join fixes the problem: SELECT T.C1, T.C2 FROM (SELECT C1, C2, ROW_NUMBER() OVER (ORDER BY C1) RN FROM T1) T, (SELECT C1, ROW_NUMBER() OVER (ORDER BY C1) RN FROM T1) T2 WHERE T2.C1=4 AND T.RN BETWEEN (T2.RN -1) AND (T2.RN +1);
C1 C2 ---------- ----- 5 Shus 4 Shrek 3 Wow
A quick test: DELETE FROM T1 WHERE C1=3;
The first method results in: C1 C2 ---------- ----- 4 Shrek 5 Shus 6 What?
The second method results in: C1 C2 ---------- ----- 5 Shus 4 Shrek 2 Hi
Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
> Tha sort is not by the ID - it's by another parameter in the table > (date..)
Maybe Analytic Functions can help:
select * from ( SELECT some_table.*, lead(ID) over (order by date) as LEAD_ID, lag(id) over (order by date) as LAG_ID from some_table ) some_table_plus where ID = :search_id or LEAD_ID = :search_id or LAG_ID = :search_id order by date
