Page 1 of 1

Using DISTINCT but at the same time getting all rows

Posted: Thu Jan 06, 2005 5:46 pm
by kzar
I'm trying to figure out how to get all the rows from a table but only ones with a unique SlotName.

I tried this from random searching on the net

Code: Select all

SELECT DISTINCT(SlotName),* from tt_Slot
	ORDER BY SlotName
but get a parse error.

Is it possible to do? Thanks

Posted: Thu Jan 06, 2005 5:55 pm
by markl999
You wouldn't get a (php) parse error with a query alone, that would be an invalid query syntax error.
If it really is a parse error can you post the full php line/code that contains the query?

Posted: Thu Jan 06, 2005 5:58 pm
by kzar
sorry by parse error I ment a mysql parse error or syntax error. I thought parse error and syntax errors are the same thing.

Posted: Thu Jan 06, 2005 5:59 pm
by feyd

Code: Select all

SELECT  a. * 
FROM tt_Slot a
LEFT  JOIN tt_Slot b ON b.SlotName = a.SlotName AND b.id != a.id
WHERE b.SlotName IS  NULL
seems to work, for getting only unique values.

However, mark is correct in a parse error coming from something else.

Posted: Thu Jan 06, 2005 6:04 pm
by kzar
It doesn't say parse error anywhere. The error is a mysql syntax error, more specificaly, this.

Code: Select all

Could not run query: You have an error in your SQL syntax near '* from tt_Slot ORDER BY SlotName' at line 1
I just said parse error becasue I thought parse errors and syntax errors where basicaly the same thing. I know a php parse error and a mysql syntax error isnt the same thing though so stop telling me.

Posted: Thu Jan 06, 2005 6:08 pm
by markl999
I think you'll need to use a GROUP BY clause rather than a DISTINCT, i.e along the lines of 'SELECT * FROM tt_Slot GROUP BY SlotName ORDER BY SlotName'

This is a common problem and the above is a guess at the solution as i can't remember the exact syntax. If it still doesn't work i'll go look up the correct format ;)

Posted: Thu Jan 06, 2005 6:17 pm
by feyd
try my code, it only finds the rows which are unique in their slot name. Meaning, it excludes all rows that have sibling slot names. Which is what I think you asked.. at least that's how I read it.

Posted: Thu Jan 06, 2005 6:22 pm
by kzar
This works fine. Sorry feyd I didn't see that your code was any different to the one I posted till just now, didn't mean to ignore it!

Code: Select all

SELECT * from tt_Slot
			GROUP BY SlotName
			ORDER BY SlotName


Thanks for the help