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

[Jay Strauss <MAPSme@heyjay.com>]

Dylan Beaudette wrote:
> 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!
In oracle it would be like (maybe it's the same in MySql)

select * from component
where (mutkey,compct_r) = (
select mutkey, max(compct_r)
  from component
 group by mutkey)
_______________________________________________
vox-tech mailing list
vox-tech@lists.lugod.org
http://lists.lugod.org/mailman/listinfo/vox-tech