oracle rownum
odelya
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?
Shakespeare
"odelya" <be.sp...@gmail.com> schreef in bericht
news:1187705463....@k79g2000hse.googlegroups.com...
where id between <yourval> -1 and <yourval> +1 ?
Shakespeare
odelya
> "odelya" <be.spec...@gmail.com> schreef in berichtnews:1187705463....@k79g2000hse.googlegroups.com...
>
> - -
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?
Shakespeare
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....
Shakespeare
Shakespeare
"Shakespeare" <wha...@xs4all.nl> schreef in bericht
news:46caf9b5$0$244$e4fe...@news.xs4all.nl...
I meant ROWID where I put rownum...
Shakespeare
odelya
Tha sort is not by the ID - it's by another parameter in the table
(date..)
Thomas Kellerer
> 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
fitzj...@cox.net
> 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.
David Fitzjarrell
Shakespeare
>
>
> 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.
Shakespeare
Shakespeare
"Thomas Kellerer" <FJIFAL...@spammotel.com> schreef in bericht
news:5j0dshF...@mid.individual.net...
Right, that's what I was looking for but could not find anymore!
Shakespeare
Wiktor Moskwa
>
> Tha sort is not by the ID - it's by another parameter in the table
> (date..)
>
LEAD and LAG analytic functions will be your friends.
--
Wiktor Moskwa
Shakespeare
Go to Morgans Library at http://www.psoug.org and look up the LAG function!
Use LAG twice: with offsets of 1 and -1 and OVER (ORDER BY
<your_date_column>)
Shakespeare
What's in a LAG?
Shakespeare
I stand corrected, one LEAD and one LAG, thanks Wiktor!
Shakespeare
Charles Hooper
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;
C1 C2 RN
---------- -------------------- ----------
1 Hello 1
2 Hi 2
3 Wow 3
4 Shrek 4
5 Shus 5
6 What? 6
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.
Martin T.
>
> 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
br,
Martin