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
