[SOLVED] Using DISTINCT but at the same time getting all row

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
kzar
Forum Newbie
Posts: 16
Joined: Thu Nov 25, 2004 3:03 pm

Using DISTINCT but at the same time getting all rows

Post 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
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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?
kzar
Forum Newbie
Posts: 16
Joined: Thu Nov 25, 2004 3:03 pm

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
kzar
Forum Newbie
Posts: 16
Joined: Thu Nov 25, 2004 3:03 pm

Post 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.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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 ;)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
kzar
Forum Newbie
Posts: 16
Joined: Thu Nov 25, 2004 3:03 pm

Post 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
Post Reply