l i n u x - u s e r s - g r o u p - o f - d a v i s
L U G O D
 
Next Meeting:
November 4: Social gathering
Next Installfest:
TBD
Latest News:
Oct. 24: LUGOD election season has begun!
Page last updated:
2004 Nov 22 19:06

The following is an archive of a post made to our 'vox-tech mailing list' by one of its subscribers.

Report this post as spam:

(Enter your email address)
[vox-tech] yet another SQL question...
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[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
vox-tech@lists.lugod.org
http://lists.lugod.org/mailman/listinfo/vox-tech



LinkedIn
LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
facebook
LUGOD Group on Facebook
'Like' LUGOD on Facebook:

Hosting provided by:
Sunset Systems
Sunset Systems offers preconfigured Linux systems, remote system administration and custom software development.

LUGOD: Linux Users' Group of Davis
PO Box 2082, Davis, CA 95617
Contact Us

LUGOD is a 501(c)7 non-profit organization
based in Davis, California
and serving the Sacramento area.
"Linux" is a trademark of Linus Torvalds.

Sponsored in part by:
Appahost Applications
For a significant contribution towards our projector, and a generous donation to allow us to continue meeting at the Davis Library.