MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:gerald_clark Date:January 15 2003 9:49pm
Subject:Re: how do i retrieve distinct rows using IN
View as plain text  
SELECT SUM( price ) from sys_bld_foo where catNum IN ('item1', 'partA', 
'partB') group by catNum;

Ken Easson wrote:

>hello,
>
>I have a list of things ('item1', 'partA', 'partB', 'partB')
>which relate to catNum items in a table sys_bld_foo.
>
>table sys_bld_foo:
>component:  varchar 16
>catNum: varchar 16
>price: decimal (10,2)
>
>i want to return 1 row for each item:
>when i use:
>SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN ('item1', 'partA', 'partB',
> 'partB');
>
>my return value ignores the second partB - and my price is short.
>
>when i use:
>SELECT price FROM sys_bld_foo WHERE catNum IN ('item1', 'partA', 'partB', 'partB');
>
>my return value doubles up on partA and partB - and my price is too much.
>
>here is a sample database to see where the problem lies:
>component         | item     |  price
>-----------------------------------------------------
>foo                | item1   |  200.00
>foo                     | item2   | 300.00
>primary bar         | partA   |   75.00
>primary bar         | partB   | 150.50 
>second bar         | partA    |  75.00
>second bar         | partB   |  150.50
>second bar         | partC    | 160.00
>
>currently i am using perl to remove the second partB, create a hash $catNum{partB} =
> 2, and then for each item returned, check the hash and multiple the price by it's results.
> This seems extremely slow if my problem can be solved in sql alone.
>can anyone help?
>
>ken easson
>justken.net
>ken@stripped
>justken web programming and technical support. 
>
>
>---------------------------------------------------------------------
>Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/           (the list archive)
>
>To request this thread, e-mail <mysql-thread130067@stripped>
>To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>  
>


Thread
how do i retrieve distinct rows using INKen Easson15 Jan
  • Re: how do i retrieve distinct rows using INgerald_clark15 Jan
Re: how do i retrieve distinct rows using INBrian Lindner15 Jan
  • Re: how do i retrieve distinct rows using INKen Easson16 Jan
    • Re: how do i retrieve distinct rows using INKeith C. Ivey16 Jan
Re:how do i retrieve distinct rows using INnossareh16 Jan