SOLVED Conditionally select from duplicate rows with thanks in the last thread

[niche]

This query:

SELECT           plan.*, trkg.curwipprice_online, trkg.addr FROM           plan LEFT   JOIN	          trkg ON           plan.cliorder = trkg.cliorder AND           trkg.addr = '$addr_clear'	WHERE 	          (TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) )ORDER BY           item_name ASC, ((1-(trkg.curwipprice_online/plan.edprice))*100) DESC

Produces this output:

 

Item Name.............PriceUSS Constitution ........1.75USS Constitution ........7.55Peterbilt 353 ........94.92Wonder Woman........1.65Wonder Woman........14.22H.M.S. Beagle ........31H.M.S. Beagle ........33.77

 

I need this output:

 

Item Name.............PriceUSS Constitution ........1.75Peterbilt 353 ........94.92Wonder Woman........1.65H.M.S. Beagle ........31

 

How do I eliminate the dupes and keep the item_names with the lowest price with SQL or do I need to do that with PHP?

Edited July 20, 2013 by niche

[birbal]

You can use 'DISTINCT' keyword with GROUP BY clause

[justsomeguy]

You can use MIN to get the minimum value, and group by the other column.

[niche]

I added GROUP BY which produces 1 selection per item_name (so far so good).

SELECT 		plan.*, trkg.curwipprice_online, trkg.addr 	FROM 		plan 	LEFT     JOIN			trkg 	ON 		plan.cliorder = trkg.cliorder 	AND 		trkg.addr = '$addr_clear'		WHERE 			(TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) )	GROUP BY 		item_name

How does DISTINCT fit? The manual talks about SELECT DISTINCT, but won't that limit columns?

 

http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

[niche]

You can use MIN to get the minimum value, and group by the other column.

I had already tried MIN as in:

GROUP BY 		item_nameHAVING 		MIN(((1-(trkg.curwipprice_online/plan.edprice))*100)

This was a nogo. Was I using it correctly?

Edited July 18, 2013 by niche

[justsomeguy]

No, I would use MIN in the select list. I'm confused though, you showed output with 2 columns but you're selecting at least 3. If your output has 2 columns then what I suggested is easy. If not then one way would be to join on a temporary table where you use the min on the temporary table, and then there's only 1 row to join on.

[niche]

I'm confused though, you showed output with 2 columns but you're selecting at least 3.

 

That's just the PHP talking after the query.

 

Anyway, I like your idea of a temp table ((I haven't figured-out how to get birbal's answer to work).

 

I created a temp table. Here's the contents

item .... Price

fish .... 50.00dog .... 1.00dog .... 2.00cat .... 5.00cat .... 10.00

 

This query obviously only returns one row (per JSG's #6):

 

SELECT *, MIN(price) FROM test6 ORDER BY item DESC

 

 

How do I use MIN to only SELECT the rows with the lowest price per item?

[justsomeguy]

I would populate the table with only the lowest price per item, and then do a regular join.

 

SELECT a.*, lowest.price FROM a INNER JOIN (SELECT title, MIN(price) AS price FROM b GROUP BY title) AS lowest ON a.title=lowest.title

[niche]

Please confirm that you're not talking about a temp physical table. You're talking a temp virtual table (called lowest in this case).

[justsomeguy]

Right.

[niche]

Based on my OP (please disregard my #7 post).

 

Here's what I have so far:

SELECT 		*	FROM 		plan	LEFT OUTER JOIN 		(SELECT 			cliorder, MIN(curwipprice_online) AS curwipprice_online        FROM 			trkg        WHERE 			addr = '$addr_clear'         GROUP            BY cliorder 		) AS x    ON 		x.cliorder = plan.cliorder

I'm pretty close except that I need each selected row to produce an array that ends with these keys (see my OP):[68]=> string(4) "0.47" ["curwipprice_online"]=> string(4) "0.47"[69]=> string(14) "1840 Pawnee St" ["addr"]=> string(14) "1840 Pawnee St"

 

The current query selects rows that produce an array that ends with:[68]=> NULL[69]=> NULL ["curwipprice_online"]=> NULL }

 

I don't think the order matters as much as that missing key.How do I get the ["addr"] key from the table called trkg, into the selection results? I tried adding ", addr" in the second select, but nogo.

Edited July 18, 2013 by niche

[justsomeguy]

Join with the trkg table and select that column from it.

[niche]

I reversed the join, used a suggestions from from birbal and jsg, and a lot of trial and error. Voila!

SELECT 	plan.*, min(trkg.curwipprice_online) AS curwipprice_online, trkg.addrFROM 	trkgRIGHT OUTER JOIN 	planON 	plan.cliorder = trkg.cliorderAND 	trkg.addr = '$addr_clear'	WHERE 	TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) )GROUP BY 	plan.item_name;Thanks guys.