alternative way to DISTINCT

Question

hi all ...

i have sql statement like this

SELECT DISTINCT results_sp_08.material_number FROM results_sp_08
  INNER JOIN courses ON results_sp_08.material_number = courses.material_number
  INNER JOIN users ON results_sp_08.id = users.id
  AND results_sp_08.doctor = 'xx260'

i need alternative way to DISTINCT in to use it in MySQL version: 4.1.22 ?

is there another way or trick?

Answer 1

I usually use a double-join strategy on these:

JOIN tablex AS x1 ON whatever  
LEFT JOIN tablex AS x2 ON whatever  
    AND x2.pkid < x1.pkid AND x2.somefield IS NULL

which guarantees I get at most 1 record on all my joins.

Answer 2

DISTINCT is functionally equivalent to GROUP BY <all the columns in your select list>

Answer 3

I've successfully eliminated DISTINCT by adding more clauses to my WHERE in the past. You need to look at what fields in your JOINs are causing multiple rows to come back. A subquery may work as well.

Answer 4

Like Joel, your best bet is to add the GROUP BY clause. In your case

GROUP BY results_sp_08.material_number

Answer 5

i need to DISTINCT to select non-repeated elements like in example i have in one column contain these values (EE432 - EE432 - EE352 - EE352) my sql statement will return only EE432 and EE352 .

so i need alternative way to do this

Answer 6

Without knowing the tables structure it is hard to tell. You need to say in words what you are trying to do.

Assuming that the result_sp_08 contains only unique "material_number"s, and you are trying to get all the unique material_number that had at least one row in courses and users tables:

SELECT results\_sp\_08.material\_number
FROM results\_sp\_08
WHERE results\_sp\_08.doctor = 'xx260' and
EXISTS(select * from courses
    where results\_sp\_08.material\_number = courses.material\_number) and
EXISTS(select * from users
    where results\_sp\_08.id = users.id)

See the documentation: http://dev.mysql.com/doc/refman/4.1/en/exists-and-not-exists-subqueries.html

Answer 7

i think we're missing some crucial information here. DISTINCT is perfectly valid in mysql 4.1.22 so why do you need an alternative?

does the material_number column contain the literal string "EE432 - EE432 - EE352 - EE352", or is your query returning 4 rows? if it's a string, no amount of SQL will help you.