MySQL Distinct and Group By

[scim1971]

Hi,

I've got a table that has:
a unique ID (proId)
a product ID (proawningId)
a "upto" width column (prowidth)
an "arm length" column (proarms)

The table is for patio awnings and it stores what projection arm length you'll need for various widths of awnings. For instance if you want an awning 2.75 metres width you look at the data find your width in the width column (although this column shows the maxium width for each awning and arm combination) so an entry might be upto width 2.98 = arm 2.5m for awning type 1.

What I'm trying to do is to create a quick look-up that populates a select drop down list. A user enters their width and clicks submit the system then queries the database and populates the select list with the possible "arm" lengths they can have.

This is the SQL statement I'm currently using:

SELECT proarms, proId, proawningId, prowidth FROM ukawnings.tblawningprojections WHERE proawningId = awningId AND prowidth >= (this is the width entered on the form) GROUP BY proawningId ORDER BY proarms ASC

This almost works but I get a separate entry for each arm length for each awning type, e.g.

if I enter 2.75m as my width the select list looks like:

2.5M (awning type 1)
2.5M (awning type 2)
2.5M (awning type 3)
3.0M (awning type 4)
3.5M (awning type 5)

What I'd like to happen is there to be only one entry for each Arm length (so only one 2.5M in the example above). I tried adding DISTINCT(proarms) to the SQL but it doesn't make any difference.

Can anyone help.
Thanks.

[topr8]

SELECT proarms FROM ukawnings.tblawningprojections WHERE prowidth >= (this is the width entered on the form) GROUP BY proarms ORDER BY proarms ASC

[scim1971]

Hi topr8

thanks for your reply. Unfortunately this doesn't work as it shows "all" arms greater than the width entered rather than just the one "upto" the width I've entered.

[topr8]

i'm a bit lost as to what you are trying to do, but try putting LIMIT 1 at the end of the query, this will just give you one result