Basic SQL Comparing

Question

There exists a table called Species which includes Sp_name, Sp_woodtype, Sp_maxht (species' max height) and I am asked this question:

For each species with the same woodtype (sp_woodtype) compare their max height. The output should include the species wood type, species name of the larger height and the species name of the smaller height.

How should this be done?

Edit 1: I have tried:

SELECT sp_woodtype, sp_name a AS Larger, sp_name b AS Smaller
FROM Species
WHERE a.sp_woodtype = b.sp_woodtpye AND a.sp_maxht>b.sp_maxht;

SO is not a site to have people do your homework. What have you tried? — Crazy Cucumber, May 23 '17 at 19:11
SELECT sp_woodtype, sp_name a AS Larger, sp_name b AS Smaller FROM Species WHERE a.sp_woodtype = b.sp_woodtpye AND a.sp_maxht>b.sp_maxht; — betamech, May 23 '17 at 19:12
Also, https://stackoverflow.com/questions/44142731/granddaughter-of-a-tree-sql-query - the table names and rows seem awfully similar as in that question. — Crazy Cucumber, May 23 '17 at 19:17
The task is not really clear. "species name of the larger height and the species of the smaller height" sounds as if there can be just two entries per wood type. I guess it's supposed to mean "species name with the largest height and the species name with the smallest height"? — Thorsten Kettner, May 23 '17 at 19:30

score 0 · Answer 1 · answered May 23 '17 at 19:13

You didn't tag your question with a specific RDBMS tag, but almost any modern RDBMS should support window functions (with mysql being the notable exception). Assuming your RDBMS supports them, rank() should do the trick here:

SELECT sp_woodtype, sp_name, sp_maxht
FROM   (SELECT sp_woodtype, sp_name, sp_maxht,
               RANK() OVER (PARTITION BY sp_woodtype 
                            ORDER BY     sp_maxht ASC) AS ra,
               RANK() OVER (PARTITION BY sp_woodtype 
                            ORDER BY     sp_maxht DESC) AS rd
        FROM   species) t
WHERE  1 IN (ra, rd)

My bad, forgot to mention that it should be in MySQL. – betamech May 23 '17 at 19:16 — betamech, May 23 '17 at 19:16

score 0 · Answer 2 · answered May 23 '17 at 19:25

First find the minimum and maximum sp_maxht per sp_woodtype. Based on these you select the rows:

select s.*
from 
(
  select 
    sp_woodtype, 
    min(sp_maxht) as min_sp_maxht,
    max(sp_maxht) as max_sp_maxht
  from species 
  group by sp_woodtype
) agg
join species s on  s.sp_woodtype = agg.sp_woodtype
               and s.sp_maxht in (agg.min_sp_maxht, agg.max_sp_maxht)
order by s.sp_woodtype, s.sp_maxht;

Basic SQL Comparing

2 Answers2