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:
September 2: Social gathering
Next Installfest:
TBD
Latest News:
Aug. 18: Discounts to "Velocity" in NY; come to tonight's "Photography" talk
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)
Re: [vox-tech] yet another SQL question...
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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
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:
EDGE Tech Corp.
For donating some give-aways for our meetings.