The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.

asked 27 Oct '16, 03:56

ontsnapt's gravatar image

ontsnapt
163
accept rate: 0%

edited 27 Oct '16, 06:58

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824

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

(27 Oct '16, 10:33) Nick Elson S...

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...

(27 Oct '16, 10:57) Volker Barth

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

permanent link

answered 27 Oct '16, 12:50

Volker%20DB-TecKy's gravatar image

Volker DB-TecKy
221127
accept rate: 44%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28

question asked: 27 Oct '16, 03:56

question was seen: 142 times

last updated: 27 Oct '16, 12:50