I thought I might be able to use a sequence object with each select, but 
unfortunately, those only update once per call, not once per row:

select sequence_next('bob'), name from TOOLS..addresses
1, "Archie"
1, "Betty"
1, "Veronica"


select sequence_next('bob'), name from TOOLS..addresses
2, "Archie"
2, "Betty"
2, "Veronica"


Cameron Knowlton
iGods Internet Marketing Inc.
came...@igods.com
P: 250.382.0226




> On 07-Feb-18, at 10:53 AM, Cameron Knowlton <camer...@igods.com> wrote:
> 
> 
> I am attempting to analyze a set of race results, comparing the top 10 
> placements for each country from one set of race results (e.g. the most 
> Recent race [#20]) to the the top 10 placements of any other particular race 
> (the Previous race [#1]).
> 
> I have created a highly simplified Race Comparison Model below to illustrate 
> my goal. The case that I have illustated here is a highly simplified model; 
> the actual solution is complex, and the database is extremely large.
> 
> I have included a screenshot of the query result, to help illustate my 
> challenge.
> 
> 
> This approach has worked great for years in the past, but the nature of the 
> data has recently changed. Specifically (and keeping to the analogy), I now 
> have data in the table not just for the top athlete from each country for 
> each race, but for all athletes from each country.
> 
> As a result, the full outer join approach that I was using successfully up 
> until now does what you would expect: it creates additional irrelevant rows 
> ([count of recentRace.country=X] x [count of prevRace.country=X]). Up until 
> recently, count of recentRace.country=X and prevRace.country=X were always 1, 
> so 1x1=1, no problem. Now, I need a new solution.
> 
> Canada is a good example here: I need returned only 2 rows:
> 
> [athleteName/athleteCountry/recentPosition/recentRaceNumber/prevPosition/prevRaceNumber]
> Adam/Canada/1/20/1/1
> Charlie/Canada/7/20/9/1
> 
> (Yes, I understand that Charlie did not get the #9 position in race 1 [it was 
> Bob, go Bob!], but that is OK, we are interested in the Country more than the 
> Athlete.)
> 
> Instead, the call returns 4 rows for Canada: 2 recentRace rows x 2 prevRace 
> rows, just as you would expect a full outer join to do in this case:
> 
> [athleteName/athleteCountry/recentPosition/recentRaceNumber/prevPosition/prevRaceNumber]
> Adam/Canada/1/20/1/1
> Adam/Canada/1/20/9/1
> Charlie/Canada/7/20/1/1
> Charlie/Canada/7/20/9/1
> 
> 
> 
> The solution that I came up with was to further correlate the 2 sets ON 
> prevRace.rowNumber = recentRace.rowNumber , which should align the 1st row 
> from recentRace with the 1st row from prevRace, the 2nd row with the 2nd row, 
> etc. (I had not yet resolved what would happen when one return set was bigger 
> than the other, but I had a workaround in mind if I got that far.)
> 
> However, there is no rownumber() function in Virtuoso. Unfortunately, 
> creating stored procedures around these tables is NOT an option (for several 
> reasons).
> 
> The beauty of using the full outer join is that I get a row even when there 
> is a country represented in the prevRace that did not place top 10 in the 
> recentRace (e.g. Henry), and vice versa (e.g. Ian). That is essential, as I 
> need to know which countries dropped out of the rankings between the prevRace 
> and the recentRace.
> 
> Since I need multiple columns returned from the prevRace table, I am not able 
> to simply use a nested select in the top select statement list of return 
> columns (i.e.:
> 
> 
> select
>       (select position from POSITIONS..raceresults PR where PR.raceNumber = 1 
> and PR.position <= 10 and PR.athleteCountry = R.athleteCountry) as 
> prevPosition,
>       R.*
> from
>       POSITIONS..raceresults R
> where
>       R.raceNumber = 20 and
>       R.position <= 10
> order by
>       R.athleteCountry,
>       R.position
> 
> 
> Even if I did decide to use one nested select statement for *every column* 
> that I need from prevRace (sinfully inefficient!), the call would not return 
> rows from prevRace where there was not a corresponding row from recentRace 
> (e.g. Henry). Using either a left outer or right outer join would sacrafice 
> unique rows from the other table.
> 
> Is there some piece of Virtuoso or SQL magic that I am missing here? Without 
> rownumber(), I cannot see a way around this.
> 
> Thanks for your help.
> 
> Cameron Knowlton
> 
> ------------------------------------------
> -- Race Comparison Model
> -- Virtuoso SQL
> -- Wed, Feb 7, 2018
> -- Cameron Knowlton
> ------------------------------------------
> 
> -- drop table POSITIONS.EXTRANET.raceresults;
> create table POSITIONS.EXTRANET.raceresults (
>       athleteName                             varchar,
>       athleteCountry                  varchar,
>       raceNumber                              integer,
>       position                                integer,
>       raceTime                                float
>       );
> 
> 
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Adam', 'Canada', 1, 1, 18.5);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Bob', 'Canada', 1, 9, 29.4);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Charlie', 'Canada', 1, 12, 35.6);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('David', 'United States', 1, 21, 48.7);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Evan', 'United States', 1, 8, 28.9);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Fred', 'Germany', 1, 3, 21.0);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('George', 'Germany', 1, 15, 38.8);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Henry', 'England', 1, 7, 28.2);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Ian', 'Scotland', 1, 14, 38.1);
> 
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Adam', 'Canada', 20, 1, 17.5);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Bob', 'Canada', 20, 11, 34.0);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Charlie', 'Canada', 20, 7, 27.7);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('David', 'United States', 20, 9, 30.1);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Evan', 'United States', 20, 8, 29.0);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Fred', 'Germany', 20, 2, 19.4);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('George', 'Germany', 20, 12, 35.6);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Henry', 'England', 20, 13, 38.8);
> insert into POSITIONS..raceresults (athleteName, athleteCountry, raceNumber, 
> position, raceTime) values ('Ian', 'Scotland', 20, 6, 26.5);
> 
> 
> 
> select
> 
>       coalesce(recentRace.athleteName, prevRace.athleteName) as athleteName,
>       coalesce(recentRace.athleteCountry, prevRace.athleteCountry) as 
> athleteCountry,
>       recentRace.position as recentPosition,
>       recentRace.raceTime as recentRaceTime,
>       recentRace.raceNumber as recentRaceNumber,
>       
>       prevRace.position as prevPosition,
>       prevRace.raceTime as prevRaceTime,
>       prevRace.raceNumber as prevRaceNumber
> 
> from
>       (
>       select
>               *
>       from
>               POSITIONS..raceresults R
>       where
>               R.raceNumber = 20 and
>               R.position <= 10
>       order by
>               R.position
>       ) recentRace
> 
> full outer join
> 
>       (
>       select
>               *
>       from
>               POSITIONS..raceresults PR
>       where
>               PR.raceNumber = 1 and
>               PR.position <= 10
>       order by
>               PR.position
>       ) prevRace
> 
> on
> 
>       prevRace.athleteCountry = recentRace.athleteCountry
> 
> order by
>       2
> 
> 
> Query result:                                                 
> VARCHAR       VARCHAR INTEGER DOUBLE PRECISION        INTEGER INTEGER DOUBLE 
> PRECISION        INTEGER
> athleteName   athleteCountry  recentPosition  recentRaceTime  
> recentRaceNumber        prevPosition    prevRaceTime    prevRaceNumber
>  Adam  Canada  1       17.5    20      1       18.5    1
>  Adam  Canada  1       17.5    20      9       29.4    1
>  Charlie       Canada  7       27.7    20      1       18.5    1
>  Charlie       Canada  7       27.7    20      9       29.4    1
>  Henry         England         <DB NULL>       <DB NULL>       <DB NULL>      
>  7       28.2    1
>  Fred  Germany         2       19.4    20      3       21      1
>  Ian   Scotland        6       26.5    20      <DB NULL>       <DB NULL>      
>  <DB NULL>
>  Evan  United States   8       29      20      8       28.9    1
>  David         United States   9       30.1    20      8       28.9    1
> No. of rows in result: 9                                                      
> 

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/virtuoso-users

Reply via email to