Welcome to WebmasterWorld Guest from 54.162.239.134

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

UNIQUE or DISTINCT MySQL/PHP Queries

How can I cull out the lowest cost item from a list of similar items

     
8:52 pm on Mar 4, 2004 (gmt 0)

10+ Year Member



Hello, all!

I have a product database (provided by a partner site) which has 10,000+ items. These items are identified by a unique ID number, which in this case is the manufacturers' product items. To do away with EXTENSIVE "massaging" of the data, I am "dumping" the raw text-delimited data into our MySQL database, in its own unique table. I query the database to display different types of items, and want to do it MORE EFFICIENTLY than the structure of the data allows.

For example, I want to show all the "cars." A user clicks on the "cars" page. I then show a list of brands, such as "Ford" and "Dodge." The user selects a brand, and then I show individual models, such as "Mustang," and "Escort," and "Thunderbird."

Here's the conundrum: The database may have several dozen "versions" of a "Mustang." When I show the different individual models, I want to JUST show the lowest priced model. For example, I just want to show the $8999 Escort, the $19999 Mustang, etc., NOT all the variants (which introduce a lot of options and stuff which takes longer to query and display).

The data, as it's "dumped" into the MySQL table, has nothing to distinguish the lowest price variant from the highest price variant, except for two key fields: The price, and the "product line." The Price is the different value which I can order my query by, and the product line is COMMON to all the variants of a particular model. So, you can have 20 "variants" of an Escort, each delineated by price, but all having a common "product line" description of "Ford Escort."

I need to group them by product line, THEN pick ONLY the lowest price version from the list. Is this possible using a UNIQUE or DISTINCT modifier on the "where" clause of the MySQL query? If so, what is the proper format?

Thanks in advance for any help here!

9:04 pm on Mar 4, 2004 (gmt 0)

10+ Year Member



Maybe I can clarify, by showing a small, sample database (here, comma-delimited):

MAKE, MODEL, PRICE, PRODUCT_LINE
Ford, Escort A, $8999, Ford Escort
Ford, Escort B, $9395, Ford Escort
Ford, Excort C, $10299, Ford Escort
Ford, Escort Z, $12199, Ford Escort
Ford, Mustang A, $18999, Ford Mustang
Ford, Mustang B, $21999, Ford Mustang
Ford, Mustang C, $23999, Ford Mustang
Ford, Mustang Z, $27999, Ford Mustang

In my query, I want to JUST show the "Escort A" and the "Mustang A" models on the same page, because they are the lowest price. I save the B, C and Z models to display on a following page if the user clicks on the Escort or Mustang link.

How do I sort through the data to JUST list the Escort A and Mustang A, and disregard the rest, (based on low price and unique product line description)?

Hope that clarifies!

9:28 pm on Mar 4, 2004 (gmt 0)

10+ Year Member



the way you have it there in mysql 3.23.36 this would work I think:

SELECT product_line, MIN(price)
FROM mytable
GROUP BY product_line
ORDER BY whatever

better to change your table info around if possible. The product_line field is redundant.. would be better like this:

ITEM_ID (unique)
MAKE
MODEL
PRICE

Then use:
SELECT make, model, item_id, MIN(price)
FROM mytable
GROUP BY make, model
ORDER BY whatever

If that doesn't work as expected, you may need to do:

SELECT product_line,
MIN(price) as minprice
FROM mytable
GROUP BY product_line
HAVING price = minprice
ORDER BY whatever

9:33 pm on Mar 4, 2004 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Should just be:

select distinct PRODUCT_LINE, anything_else_you_want from the_table order by PRICE

JAG

9:41 pm on Mar 4, 2004 (gmt 0)

10+ Year Member



Yah - that'll do it too with a: limit 0,1
9:43 pm on Mar 4, 2004 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



The distinct will only return 1 so no need for the limit ;-)

JAG

<added>That is if that's the only data you're bringing back otherwise you do need the limit as slade7 said</added>

10:26 pm on Mar 4, 2004 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



LIMIT
will constrain the number of rows returned from the result set, so don't use that. The way it is stated here, you will only get one row back, period.

If you want the product line and price (or any other columns), use the format suggested by slade7 in msg #3:

SELECT 
DISTINCT PRODUCT_LINE,
MIN(PRICE)
FROM mytable
GROUP BY PRODUCT_LINE
ORDER BY PRODUCT_LINE
;

If you are just building a unique list, use DISTINCT as suggested by JAG. However, he is correct in that you want to use it without any other columns, otherwise you are going to need a GROUP BY clause (not a LIMIT clause). JAG was right, but should remove the anything_else_you_want from his statement there:
SELECT 
DISTINCT PRODUCT_LINE
FROM mytable
ORDER BY PRODUCT_LINE
;

Nice tips by the way, slade7 and JAG.

2:05 am on Mar 5, 2004 (gmt 0)

10+ Year Member



Thanks coopster - I seldom use distinct.. but often use COUNT(DISTINCT column) etc...

That explains why I get errors sometimes with distinct.

<add>When I come up with a query off the top of my head, it's usually no more (or less) than 4 error messages away from working - so keep that in mind :)</add>

12:39 pm on Mar 5, 2004 (gmt 0)

10+ Year Member



Won't the price be ordered incorrectly as it is a string field, not an integer field?
12:47 pm on Mar 5, 2004 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I just assume that since the sample is a text file that the data will be in the correct format when pulled into the database. I would hope so anyway :-)

JAG

1:19 pm on Mar 5, 2004 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



True, Netizen. I seen the dollar sign in the data, but, like JAG, I made an assumption that the data was actually being stored as numeric. You can still get the sort the way you want, you just have to get a bit fancier and use MySQL STRING functions:
SELECT 
DISTINCT product_line,
MIN(ABS(SUBSTRING_INDEX(price,'$',-1))) AS price
FROM cars2
GROUP BY product_line
ORDER BY product_line
;

And if you want the dollar sign back on:
SELECT 
DISTINCT product_line,
CONCAT('$',MIN(ABS(SUBSTRING_INDEX(price,'$',-1)))) AS price
FROM cars2
GROUP BY product_line
ORDER BY product_line
;

If there were commas and decimal points in the price column, it gets even more complex. I'm with JAG, store the data as decimal and format the output. Much easier.
6:06 pm on Mar 5, 2004 (gmt 0)

10+ Year Member



Thanks, all, for the GREAT help!

I am now "tweaking" it to get it just right. BTW, the price data IS in numeric form (as a double(10,2) value), and I put the dollar sign in on the PHP page code.

Again, thanks!

7:34 pm on Mar 5, 2004 (gmt 0)

10+ Year Member



Just double checking - I don't like to make assumptions and it is a common mistake to order string fields as numbers.

Good luck with the rest.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month