Re: [vox-tech] yet another SQL question...[solved]
On Monday 22 November 2004 07:06 pm, David Hummel wrote: > On Mon, Nov 22, 2004 at 02:00:59PM -0800, Dylan Beaudette wrote: > > 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. > > I would use CREATE TABLE ... SELECT. > > I think the following SELECT will work: > > select > mukey, > max(comppct_r), > taxorder, > taxsuborder, > taxgrtgroup > from component > group by mukey; > > -David (Feels like an idiot) Thanks, -- Dylan Beaudette Soil Science Graduate Group University of California at Davis ___ vox-tech mailing list [EMAIL PROTECTED] http://lists.lugod.org/mailman/listinfo/vox-tech
Re: [vox-tech] yet another SQL question...
On Mon, Nov 22, 2004 at 02:00:59PM -0800, Dylan Beaudette wrote: > > 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. I would use CREATE TABLE ... SELECT. I think the following SELECT will work: select mukey, max(comppct_r), taxorder, taxsuborder, taxgrtgroup from component group by mukey; -David ___ vox-tech mailing list [EMAIL PROTECTED] http://lists.lugod.org/mailman/listinfo/vox-tech
Re: [vox-tech] yet another SQL question...
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 [EMAIL PROTECTED] http://lists.lugod.org/mailman/listinfo/vox-tech
[vox-tech] yet another SQL question...
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 ___ vox-tech mailing list [EMAIL PROTECTED] http://lists.lugod.org/mailman/listinfo/vox-tech
