How to use Rank

[former_SQLA_member1694909]

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

• the rank number of the animal.

• the rank number where animal = father

• the rank number where animal = mother

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.

[Former Member]

Part of the answer would normally include a Partion-By Window expression. As in this (albeit incomplete) example:

select Animal, Father, Mother, rank() over (Partion by animal order by birthDate desc) from pedigree . . .

but you will need some sort of expresson to cover all distinct cases (in addition to just the bare "animal" column ).
One possible complication could be due to your first 'all'/generic rank( ) may need a union all with another query and a possible additional term (or 2) in the final order by clause; as in:

order by animal, birthDate asc;

or

order by <expression>, birthDate asc;

.

.

.

just a couple of thoughts that may help

[VolkerBarth]

I'd suggest to add the according table schema and a few sample entries (i.e. a CREATE TABLE and a bunch of INSERT statements) to give us something to work on... And please show us the desired output of the query w.r.t. your sample data.

That suggestion comes because I'm really not able to understand your requirements although I would guess that it's not too difficult to solve...

[former_SQLA_member1694885 · Accepted solution]

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.

HTH

Volker Stöffler DB-TecKnowledgy