Finding the next best answer
Hello All,
I have a problem where I need to find the 2nd and 3rd best answers as
well as the best, and wrap them all up into a single result row. I'm
having trouble getting to an IDS-compliant answer:
(tables simplified to protect the sanity of the innocent)
Table tab1 has two columns: an integer id (obj1), and a character
description (name)
Table link has three columns: an integer id (obj1) which corresponds to
the column of the same name in tab1, an integer (obj2) which is just
data, and an integer (pri) which specifies a priority of the link
between the object in tab1 (obj1) and the data. tab1 is 1-many with
link on obj1.
To get the "highest" (or lowest, for a given definition of down...)
priority data for each obj1 is easy:
select tab1.name, sub1.obj1, sub1.pri
from tab1 inner join table(multiset(
select obj1, min(pri)
from link
group by obj1
)) as sub1 (obj1, pri)
on tab1.obj1 = sub1.obj1
...but to get the 2nd and third is proving surprisingly difficult. I
cant use first or order by statements in my subqueries, which would
make it fairly easy, so I tried the somewhat convoluted:
select tab1.name, sub1.obj1, sub1.pri, sub2.pri
from tab1 left outer join table(multiset(
select obj1, min(pri)
from link
group by obj1
)) as sub1 (obj1, pri)
on tab1.obj1 = sub1.obj1
left outer join table(multiset(
select obj1, min(pri)
from link inner join sub1
on link.obj1 = sub1.obj1
where sub1.pri != link.pri
group by obj1
)) as sub2 (obj1, pri)
on tab1.obj1 = sub2.obj1
...thinking to find the minimum again while excluding the first result.
But to exclude the result from the first subquery required me to make
it a correlated subquery referring back to the pseudo-table of the
first result (sub1) and (in addition to ringing efficiency danger
bells) IDS doesn't seem to want to let me do that. Can anyone suggest
a method to get the back-referrence to work, or - better really by far
- just any clever way to get the 2nd and 3rd best answers all in the
one row?
Thanks for any advice,
- rob.
I did the following and it seems to work well. I don't think the
performance will be too awful but I don't know. Notice that I used real
views which simplifies the logic to the point that even I can
understand it. This doesn't handle ties. The terminology is from horse
racing and is used somewhat incorrectly in that place means either
first or second and show means either first, second, or third.
create table tab1 (
obj1 serial,
name varchar(10)
) ;
create table link (
obj1 integer,
obj2 integer,
pri integer
) ;
create view win_pri(obj1, win_pri) as
select
obj1,
min(pri)
from
link
group by obj1;
create view place_pri(obj1, place_pri) as
select
l.obj1,
min(pri)
from
link l,
win_pri mp
where
pri > mp.win_pri
group by
l.obj1;
create view show_pri(obj1, show_pri) as
select
l.obj1,
min(pri)
from
link l,
place_pri pp
where
pri > pp.place_pri
group by
l.obj1;
insert into tab1 values (0,"test1");
insert into tab1 values (0,"test2");
insert into tab1 values (0,"test3");
insert into link values (1,85,0);
insert into link values (1,85,1);
insert into link values (1,85,2);
insert into link values (1,85,3);
insert into link values (1,85,4);
insert into link values (2,86,0);
insert into link values (2,86,1);
select * from tab1;
select * from link;
select * from win_pri;
select * from place_pri;
select * from show_pri;
select
t.*,
wp.win_pri,
pp.place_pri,
sp.show_pri
from
tab1 t,
outer win_pri wp,
outer place_pri pp,
outer show_pri sp
where
t.obj1 = wp.obj1 and
t.obj1 = pp.obj1 and
t.obj1 = sp.obj1
;
Dev wrote:
> Hello All,
>
> I have a problem where I need to find the 2nd and 3rd best answers as
> well as the best, and wrap them all up into a single result row. I'm
> having trouble getting to an IDS-compliant answer:
> (tables simplified to protect the sanity of the innocent)
>
> Table tab1 has two columns: an integer id (obj1), and a character
> description (name)
> Table link has three columns: an integer id (obj1) which corresponds to
> the column of the same name in tab1, an integer (obj2) which is just
> data, and an integer (pri) which specifies a priority of the link
> between the object in tab1 (obj1) and the data. tab1 is 1-many with
> link on obj1.
>
> To get the "highest" (or lowest, for a given definition of down...)
> priority data for each obj1 is easy:
>
> select tab1.name, sub1.obj1, sub1.pri
> from tab1 inner join table(multiset(
> select obj1, min(pri)
> from link
> group by obj1
> )) as sub1 (obj1, pri)
> on tab1.obj1 = sub1.obj1
This is the same as:
SELECT tab1.name, tab1.obj1, MIN(link.pri)
FROM tab1 INNER JOIN link
ON tab1.obj1 = link.obj1
GROUP BY tab1.name, tab1.obj1;
which is, to my mind, easier to read.
>
> ....but to get the 2nd and third is proving surprisingly difficult. I
> cant use first or order by statements in my subqueries, which would
> make it fairly easy, so I tried the somewhat convoluted:
>
> select tab1.name, sub1.obj1, sub1.pri, sub2.pri
> from tab1 left outer join table(multiset(
> select obj1, min(pri)
> from link
> group by obj1
> )) as sub1 (obj1, pri)
> on tab1.obj1 = sub1.obj1
> left outer join table(multiset(
> select obj1, min(pri)
> from link inner join sub1
> on link.obj1 = sub1.obj1
> where sub1.pri != link.pri
> group by obj1
> )) as sub2 (obj1, pri)
> on tab1.obj1 = sub2.obj1>
> ....thinking to find the minimum again while excluding the first result.
> But to exclude the result from the first subquery required me to make
> it a correlated subquery referring back to the pseudo-table of the
> first result (sub1) and (in addition to ringing efficiency danger
> bells) IDS doesn't seem to want to let me do that. Can anyone suggest
> a method to get the back-referrence to work, or - better really by far
> - just any clever way to get the 2nd and 3rd best answers all in the
> one row?
Don't try to do the whole thing with inline-views, use a temporary table:
SELECT obj1, MIN(pri) AS pri, 1 AS rank
FROM link
GROUP BY 1, 3
INTO TEMP tmp WITH NO LOG;
INSERT INTO tmp
SELECT link.obj1, MIN(link.pri), 2
FROM link INNER JOIN tmp
ON link.obj1 = tmp.obj1
AND link.pri > tmp.pri
AND tmp.rank = 1
GROUP BY 1, 3;
INSERT INTO tmp
SELECT link.obj1, MIN(link.pri), 3
FROM link INNER JOIN tmp
ON link.obj1 = tmp.obj1
AND link.pri > tmp.pri
AND tmp.rank = 2
GROUP BY 1, 3;
SELECT tab1.name, tab1.obj1, x1.pri, x2.pri, x3.pri
FROM tab1
INNER JOIN tmp AS x1
ON tab1.obj1 = x1.obj1
AND x1.rank = 1
LEFT JOIN tmp AS x2
ON x1.obj1 = x2.obj1
AND x2.rank = 2
LEFT JOIN tmp AS x3
ON x2.obj1 = x3.obj1
AND x3.rank = 3;
Assuming that your not constrained to a single statement, of course.
Depending upon the size of your data, you'll probably need a unique
index on, and the proper statistics for, tmp(obj1, rank).
--
rh
Bozon said:
>the terminology is from horse racing and is used somewhat incorrectly in that place >means either first or second and show means either first, second, or third.
I use place to mean second best only and show to mean thrid place only.
Bozon said:
insert into tab1 values (0,"test1");
insert into tab1 values (0,"test2");
insert into tab1 values (0,"test3");
insert into link values (1,85,0);
insert into link values (1,85,1);
insert into link values (1,85,2);
insert into link values (1,85,3);
insert into link values (1,85,4);
insert into link values (2,86,0);
insert into link values (2,86,1);
select * from tab1;
select * from link;
select * from win_pri;
select * from place_pri;
select * from show_pri;End Bozon
These inserts and select aren't needed to solve the problem of course
they are just test data and selects to verify that I inserted the data
correctly and that I wrote the final query correctly. I always include
the whole thing so that the viewers at home can play along.
Sorry, if this makes my post somewhat confusing.