query issue

[curwen]

Hi,
I'm in deep struggle with query from a huge table:

what I've got is a lot of records like:

_______________
numb fk1
_______________
1231456 61
1231456 62
1231456 63
1231456 61

from my view I should only get the last record:
__________
123456 61

last has to be intended as inserting order, there's not sorting defined on data
the table has also a progressive id generated by a sequence..

I've tried the the following , but doesn't work:

select distinct numb,fk1
from <table>

can anybody help, please?
best regards
jc

[Guido Konsolke]

Re: query issue

"curwen" wrote...[color=blue]
> Hi,
> I'm in deep struggle with query from a huge table:
>
> what I've got is a lot of records like:
>
> _______________
> numb fk1
> _______________
> 1231456 61
> 1231456 62
> 1231456 63
> 1231456 61
>
> from my view I should only get the last record:
> __________
> 123456 61
>
> last has to be intended as inserting order, there's not sorting[/color]
defined on data[color=blue]
> the table has also a progressive id generated by a sequence..
>
> I've tried the the following , but doesn't work:
>
> select distinct numb,fk1
> from <table>
>
> can anybody help, please?
> best regards
> jc[/color]

Hi jc,

why don't you use the 'progressive id' you mentioned?
Hint for free: MAX().

hth,
Guido

[Mark C. Stock]

Re: query issue

"Guido Konsolke" <GK@oblivion.co m> wrote in message
news:1075286028 .798968@news.th yssen.com...
| "curwen" wrote...
| > Hi,
| > I'm in deep struggle with query from a huge table:
| >
| > what I've got is a lot of records like:
| >
| > _______________
| > numb fk1
| > _______________
| > 1231456 61
| > 1231456 62
| > 1231456 63
| > 1231456 61
| >
| > from my view I should only get the last record:
| > __________
| > 123456 61
| >
| > last has to be intended as inserting order, there's not sorting
| defined on data
| > the table has also a progressive id generated by a sequence..
| >
| > I've tried the the following , but doesn't work:
| >
| > select distinct numb,fk1
| > from <table>
| >
| > can anybody help, please?
| > best regards
| > jc
|
| Hi jc,
|
| why don't you use the 'progressive id' you mentioned?
| Hint for free: MAX().
|
| hth,
| Guido
|
|

additional hint: use the max(id) in a subquery

-- mcs

[curwen]

Re: query issue

> Hi jc,[color=blue]
>
> why don't you use the 'progressive id' you mentioned?
> Hint for free: MAX().
>
> hth,
> Guido[/color]

because if I do something like:

select max(n_id),n_num ber from numbers group by n_number

then I get :
_____________
max(id) numb
12 123456

I need the fk1 field, too
and

select max(n_id),n_num ber,fk1 from numbers group by n_number

is not going to work..

I accomplished the mission using:

select * from numbers where n_id in
(select max(n_id) from numbers group by n_number);

but i'm afraid it could be a bottleneck
is there any way to avoid nested queries?

PS i'm sorry for xpost :)

[Mark D Powell]

Re: query issue

"Guido Konsolke" <GK@oblivion.co m> wrote in message news:<107528602 8.798968@news.t hyssen.com>...[color=blue]
> "curwen" wrote...[color=green]
> > Hi,
> > I'm in deep struggle with query from a huge table:
> >
> > what I've got is a lot of records like:
> >
> > _______________
> > numb fk1
> > _______________
> > 1231456 61
> > 1231456 62
> > 1231456 63
> > 1231456 61
> >
> > from my view I should only get the last record:
> > __________
> > 123456 61
> >
> > last has to be intended as inserting order, there's not sorting[/color]
> defined on data[color=green]
> > the table has also a progressive id generated by a sequence..
> >
> > I've tried the the following , but doesn't work:
> >
> > select distinct numb,fk1
> > from <table>
> >
> > can anybody help, please?
> > best regards
> > jc[/color]
>
> Hi jc,
>
> why don't you use the 'progressive id' you mentioned?
> Hint for free: MAX().
>
> hth,
> Guido[/color]

JC, there is no guarantee that the order that the rows display in is
the same as the insert order since Oracle is going to look for and
insert into blocks with free space in them. Guido, pointed you at the
solution: you have to use the max progressive id for a numb fk1 value
combination to find the last inserted value pair.

HTH -- Mark D Powell --

[curwen]

Re: query issue

> JC, there is no guarantee that the order that the rows display in is[color=blue]
> the same as the insert order since Oracle is going to look for and
> insert into blocks with free space in them. Guido, pointed you at the
> solution: you have to use the max progressive id for a numb fk1 value
> combination to find the last inserted value pair.
>
> HTH -- Mark D Powell --[/color]

ok, do you mean something like this:

select * from numbers where n_id in
(select max(n_id) from numbers group by n_number);


in your opinion is there any way to avoid the nested query?
jc

[Mark D Powell]

Re: query issue

josephcurwen@de spammed.com (curwen) wrote in message news:<cc68edc4. 0401290135.4267 1458@posting.go ogle.com>...[color=blue][color=green]
> > JC, there is no guarantee that the order that the rows display in is
> > the same as the insert order since Oracle is going to look for and
> > insert into blocks with free space in them. Guido, pointed you at the
> > solution: you have to use the max progressive id for a numb fk1 value
> > combination to find the last inserted value pair.
> >
> > HTH -- Mark D Powell --[/color]
>
> ok, do you mean something like this:
>
> select * from numbers where n_id in
> (select max(n_id) from numbers group by n_number);
>
>
> in your opinion is there any way to avoid the nested query?
> jc[/color]

Observe:
SQL> select * from marktest2;

TCOL1 TCOL2
---------- ----------
1231456 1
1231456 2
1231456 3
1231456 4
3211456 1
3211456 3
3211456 2 <= notice value 2 stored physically after later
insert

7 rows selected.

1 select * from marktest2 a
2 where tcol2 = ( select max(tcol2) from marktest2 b
3* where b.tcol1 = a.tcol1 )
SQL> /

TCOL1 TCOL2
---------- ----------
1231456 4
3211456 3

The above will work well if you enter the outer query using tcol1, but
if you want a solution without a sub-query then convert the subquery
into a join:

1 select a.*
2 from marktest2 a
3 ,(select b.tcol1, max(b.tcol2) as tcol2
4 from marktest2 b
5 group by b.tcol1
6 ) c
7 where a.tcol1 = c.tcol1
8* and a.tcol2 = c.tcol2
SQL> /

TCOL1 TCOL2
---------- ----------
1231456 4
3211456 3


However, if you want all the max id's for a value then just run the
group by query and no outer query or join is necessary at all.

HTH -- Mark D Powell --

[Vladimir Andreev]

Re: query issue

>[color=blue]
> select * from numbers where n_id in
> (select max(n_id) from numbers group by n_number);
>
>
> in your opinion is there any way to avoid the nested query?[/color]

There's no way, and frankly, no need to avoid the nested query. What
you need to avoid is the double scan of the numbers table (or its
index on N_ID). And I thought you said you need a single record, not
the last record for every n_number?
Assuming that N_ID is unique, and the sequence generates unique
numbers in ascending order (nocycle), this will select the very last
record in the table using a NOSORT index access with a stopkey, and
then a single table access by index ROWID (in other words: very fast):

select * from (
select * from numbers order by n_id desc)
where rownum=1;

Note that 'the very last' only refers to the order in which sequence
numbers are generated, not necessarily the actual insertion order.
There are questions like 'what counts as insertion time -- the time
the insert statement is executed, or the time when the sequence number
is generated?' that need to be answered -- in a legal debate, for
example :-)

For all other intents and purposes, the sequence logic will do nicely.
HTH,
Flado

[kibeha]

Re: query issue

> select * from numbers where n_id in[color=blue]
> (select max(n_id) from numbers group by n_number);
>
>
> in your opinion is there any way to avoid the nested query?
> jc[/color]

In Oracle 9 there is a way :


select
n_number,
max(fk1) keep (dense_rank last order by n_id) last_fk1
from numbers
group by n_number;


What this means is, that it will only do a max(fk1) on those records
with the last n_id value within each group.
As n_id is unique, the max(fk1) will only operate on the one record
within each n_number group that has the largest n_id.


In Oracle 8 you won't avoid the nested query, but you can avoid
accessing numbers table twice :


select distinct n_number, last_fk1
from
(
select
n_number,
last_value(fk1) over (partition by n_number order by n_id rows
between unbounded preceding and unbounded following) last_fk1
from numbers
);


The analytic function will give you the fk1 value for the last n_id
within each n_number partition as you go along, but as that inner
select will return a row for each row in numbers, you select distinct
to only get the relevant information.


Hope these may help.


KiBeHa

[curwen]

Re: query issue

Hi jc,

>
why don't you use the 'progressive id' you mentioned?
Hint for free: MAX().
>
hth,
Guido

because if I do something like:

select max(n_id),n_num ber from numbers group by n_number

then I get :
_____________
max(id) numb
12 123456

I need the fk1 field, too
and

select max(n_id),n_num ber,fk1 from numbers group by n_number

is not going to work..

I accomplished the mission using:

select * from numbers where n_id in
(select max(n_id) from numbers group by n_number);

but i'm afraid it could be a bottleneck
is there any way to avoid nested queries?

PS i'm sorry for xpost :)