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