Select unique values without using distinct

[Maya Angelou]

Hello,
I would like to retrieve unique values from a database without using the distinct keyword.
Here is what I have tried till now, any help regarding this is appreciated.

SELECT prds.id,prds.nm,prds.short_name,prds.price,depts.nm AS 'dept_name' FROM prds,depts,prod_dept WHERE (prds.nm LIKE '%" . $search . "%' OR
prds.short_name LIKE '%" . $search . "%' OR      
prds.short_desc LIKE '%" . $search . "%' OR 
prds.long_desc LIKE '%" . $search . "%' OR
depts.nm LIKE '%" . $search . "%') AND 
prod_dept.prod_id=prds.id AND prod_dept.dept_id = depts.id ORDER BY prds.nm ASC

[NetworkeR]

Hello,
I think you are asking for this

SELECT prds.id,prds.nm,prds.sh_nm,prds.price,min(depts.nm) AS 'dept_name'
FROM prds,depts,prod_dept
WHERE (prds.nm LIKE '%" . $search . "%' OR
prds.sh_nm LIKE '%" . $search . "%' OR      
prds.short_desc LIKE '%" . $search . "%' OR
prds.long_desc LIKE '%" . $search . "%' OR
depts.nm LIKE '%" . $search . "%') AND
prod_dept.prod_id=prds.id AND
prod_dept.dept_id = depts.id
GROUP BY prds.id,prds.nm,prds.sh_nm,prds.price
ORDER BY prds.nm ASC

[SoftWore]

Hello,
I think the group by clause if the best choice in this situation. Also if you need to avoid the duplicate entries in your result then it can be done with the group by clause. Suppose you have a table A and it have a column X, the column C has the following values 1,2,3... etc.
Then the following query will return the unique values

Select X From A Group By X;

[InterNetWorKed]

Hello,
I have not test the below query, but I think this can work

select test.deptno
from sc.emp test
sc.emp b
where test.deptno b.deptno
and test.rowid<>b.rowid

[GlassFish]

Hello,
If you require unique values then I think you will need to use the distinct clause because it is more effective then using the group by clause
Here are few examples

SELECT DISTINCTROW cpy 
FROM cus 
INNER JOIN Orders ON Cus.CustID = Orders.CustID;

SELECT DISTINCT cpy 
FROM Cus
INNER JOIN Orders ON Cus.CustID = Orders.CustID;

[GlassFish]

Hello,
Something like this can even work

SELECT CarID
FROM TableName
GROUP BY CarID;

Or

SELECT DISTINCT CarID
FROM TableName;

[Windowed]

Hello,
If you need distinct values then you can use the following

select x.depnum from emp x emp b
where x.depnum b.depnum
and x.rowid not in (select max(rowid) from emp)
group by x.depnum;