I have a table pedigree with the columns Animal, Father, Mother, Birthdate.
All records are retrieved ordered on birthdate. Now I would like to know
So the SQL should look something like
select Animal, Father, Mother, rank() over (order by birthDate desc), rank() where animal = father over (order by birthDate desc), rank() where animal = mother over (order by birthDate desc) from pedigree order by birthDate asc;
Can someone give me a hint to get a correct SQL statement.
Assuming you want to retrieve the age rank of Animal, Father and Mother, I guess you'll have to materialize the complete ranking of all animals since any restriction defines the population upon which the rank is calculated. in the next step, you can join this materialization against the pedigree table once for the animal and once for each parent.
Assuming Animal is PK and CHAR (40):
declare local temporary table ltAgeRank (Animal char (40) not null primary key, rk unsigned int not null) not transactional;
delete from ltAgeRank;
insert into ltAgeRank select "Animal", rank () over (order by "Birthdate" desc) from "pedigree";
-- select * from ltAgeRank;
select p."Animal", p."Father", p."Mother", p."Birthdate", rank () over (order by p."Birthdate" desc), a.rk ark, f.rk frk, m.rk mrk from "pedigree" p join ltAgeRank a on p."Animal" = a."Animal" left join ltAgeRank f on p."Father" = f."Animal" left join ltAgeRank m on p."Mother" = m."Animal" order by birthdate asc;
it might also work for a table expression instead of the LT table.
Volker Stöffler DB-TecKnowledgy
answered 27 Oct '16, 12:50