The DISTINCT command with multiple columns

[itnassol]

Hi guys,

What i want to do is use a query that will return one of each type regardless of the other in there, for example.

i have a list of unit unit 3131,3132,3133 etc and they have a status attached to them of red, green or yellow.

when i run a select statment on them as below i get the results

unit 3131 - red
unit 3132 - green
unit 3132 - red

i knopw that this is becasue the DB is seeing the second two as distinct, what i want is just one of all the unit regardless of second or thrid entries.

so the statment i am using is

SELECT DISTINCT Unit, Status FROM tblAnalysis WHERE Unit LIKE '313%'

any help would be great

Thanks

Andrew

[mwasif]

Use GROUP BY like

SELECT Unit, Status FROM tblAnalysis WHERE Unit LIKE '313%'
GROUP BY Unit

Or you can use GROUP_CONCAT() to list all the status

SELECT Unit, GROUP_CONCAT(Status) FROM tblAnalysis 
WHERE Unit LIKE '313%'
GROUP BY Unit