PHP select distinct

lowndsy · 06-02-05

Hi all. having a little trouble with select distinct - I need to list the cheapest unique products based on the product name - easy peasy, that's just:
"select distinct productname from wherever order by price"
but I need the contents of the rest of the row as well and you can't do that with just select distinct. Anyone know how?

Thanks in advance,
Steve

drivetowin · 07-02-05

cant you do select distinct productname, fielda, fieldb, fieldc ..... from table order by price ?

that should still return just one row for each product

lowndsy · 07-02-05

Nope. Select distinct limits you to one field from the row. I think I either need to do a join or use MIN - I'm having another go at it now.

999gi · 07-02-05

Why dont you just drop the distinct and do

SELECT field1,field2,field3 FROM table WhERE condition ORDER BY price ASC LIMIT 1

That'll be the first one

lowndsy · 07-02-05

I'll give that a go - I've got this at the moment:

SELECT gamename, price FROM game WHERE active=1 GROUP by gamename, price ORDER by price ASC

but I've seen some worrying reports on how group is just weird and doesn't always pick the same result so although it seems to be working for me I'm going to move away from it as soon as possible. I'll go check your method out now...

lowndsy · 07-02-05

Just had a closer look at that code - unless you have something clever to put where you put "condition" it'd only return one result. I need the cheapest price for every unique product in the table so I'll have to stick with the "group" method for now.

Jack · 08-02-05

Think you need a subquery. Try this:

select top 100 * from products
where productname in
(select distinct productname from products)
order by price asc

- should give you the 100 cheapest unqiue products

999gi · 08-02-05

Oh sorry - I see what you are trying to do

One way is to select them all ordered by name then price ASC

Then as you loop through them only display the first item of the same name using an if. As they are sorted by name you just need to record the last record name field and then check that the new one is different - if it is show it, if not dont

lowndsy · 08-02-05

Yeah I thought of that but if I got a lotof traffic it would probably bring the server to its knees

so now I'm thinking of doing somethig similar in the admin section and writing the results to a fresh table for users to search. Kind of defeats the object of having a feed driven site but it'd be more reliable and reduce processing load on the server too.
The group method is up and running now but it is definitely missing some results out.