[vox-tech] yet another SQL question...

[Dylan Beaudette]

Hi everyone, 

I am trying to accomplish in SQL (the MySQL dialect to be exact), what may 
only be possible with an integrated approach... but I thought that I would 
ask:

I have a single table, called 'component'. There are records in this table 
that represent components of a larger unit. Each record is identified with a 
key linking it to the larger unit (mukey), and has a column that defines the 
percent of the larger unit that this component represents (comppct_r). There 
are other categorical attributes associated with the components as well. I 
would like to make a table that displays the dominant component (i.e. 
comppct_r is the largest for a given larger unit) and associated attributes 
for each larger unit.

here is an example query:
select component.mukey, component.comppct_r, component.taxorder, 
component.taxsuborder, component.taxgrtgroup 
from component limit 20;
+--------+-----------+-------------+-------------+--------------+
| mukey  | comppct_r | taxorder    | taxsuborder | taxgrtgroup  |
+--------+-----------+-------------+-------------+--------------+
| 467166 |        90 | Inceptisols | Xerepts     | Haploxerepts |
| 467165 |        30 |             |             |              |
| 467165 |        20 | Entisols    | Orthents    | Xerorthents  |
| 467165 |        35 | Entisols    | Orthents    | Xerorthents  |
| 467164 |        85 | Entisols    | Orthents    | Xerorthents  |
| 467163 |        85 | Entisols    | Orthents    | Xerorthents  |
| 467160 |        30 | Mollisols   | Xerolls     | Haploxerolls |
| 467160 |        60 | Alfisols    | Xeralfs     | Haploxeralfs |
| 467159 |        30 |             |             |              |
| 467159 |        20 | Entisols    | Orthents    | Xerorthents  |
| 467159 |        35 | Entisols    | Orthents    | Xerorthents  |
| 467158 |        85 | Alfisols    | Xeralfs     | Haploxeralfs |
| 467157 |        85 | Inceptisols | Xerepts     | Haploxerepts |
| 467156 |        85 | Alfisols    | Xeralfs     | Haploxeralfs |
| 467155 |        85 | Inceptisols | Xerepts     | Calcixerepts |
| 467154 |        85 | Mollisols   | Xerolls     | Argixerolls  |
| 467153 |        30 | Inceptisols | Xerepts     | Haploxerepts |
| 467153 |        20 |             |             |              |
| 467153 |        35 | Alfisols    | Xeralfs     | Haploxeralfs |
| 467152 |        85 | Alfisols    | Xeralfs     | Natrixeralfs |
+--------+-----------+-------------+-------------+--------------+

I am able to accomplish what I am interested with an intermediate step, but if 
there is a single step it would be nice.

Here is my idea for a 2 step solution to the above:
1. create table comp_max select component.mukey, max(comppct_r) from component 
group by component.mukey

2. join the records from the component table based on mukey and the max_pct 
value

...perhaps this can be done with a subselect..?

thanks in advance for any ideas!

-- 
Dylan Beaudette
Soil Science Graduate Group
University of California at Davis