grab unique rows

[aebstract]

$query = mysql_query("SELECT * FROM calendar WHERE MONTH(caldate) = '$calmonth2' && YEAR(caldate) = '$calyear' ORDER BY DAY(caldate) LIMIT 1") or DIE(mysql_error());

 

I have 4 rows, they have a field called event_tag. I want to grab 1 result from each unique event_tag. My query is obviously wrong, but I'm hoping I'm close and not much needs to be changed. Thanks

[Hybride]

$query = mysql_query("SELECT DISTINCT(event_tag) FROM calendar WHERE MONTH(caldate) = '$calmonth2' && YEAR(caldate) = '$calyear' ORDER BY DAY(caldate) LIMIT 1") or DIE(mysql_error());

ought to do it.

[kickstart]

I have 4 rows, they have a field called event_tag. I want to grab 1 result from each unique event_tag. My query is obviously wrong, but I'm hoping I'm close and not much needs to be changed. Thanks

 

How do you decide which of the 4 rows for an event tag that you want to bring back. If you just want a list of the event tags then the code Hybride gives should do fine. However if you want the (say) latest row of the 4 for an event tag then something different is required (and could do with the table layout to help there).

 

All the best

 

Keith

[aebstract]

I want all the information from the row, not just the information from that column. If I have several entries, I don't care which one it grabs, just only grab 1.

[kickstart]

Hi

 

Cheap and nasty way to do it

 

$query = mysql_query("SELECT * FROM calendar WHERE MONTH(caldate) = '$calmonth2' && YEAR(caldate) = '$calyear' ORDER BY DAY(caldate) GROUP BY event_tag LIMIT 1") or DIE(mysql_error());

 

Won't work in most flavours of SQL but MySQL seems to manage it.

 

All the best

 

Keith

[aebstract]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY event_tag LIMIT 1' at line 1

[kickstart]

Hi

 

:'( . Made a mistake. The ORDER BY clause has to go after the GROUP BY clause

 

$query = mysql_query("SELECT * FROM calendar WHERE MONTH(caldate) = '$calmonth2' && YEAR(caldate) = '$calyear' GROUP BY event_tag ORDER BY DAY(caldate) LIMIT 1") or DIE(mysql_error()); 

 

Although I suspect you don't want the LIMIT 1 there (which was in your original SQL.

 

$query = mysql_query("SELECT * FROM calendar WHERE MONTH(caldate) = '$calmonth2' && YEAR(caldate) = '$calyear' GROUP BY event_tag ORDER BY DAY(caldate)") or DIE(mysql_error()); 

 

All the best

 

Keith

[aebstract]

Okay yeah that worked. How come the LIMIT 1 is removed?

[kickstart]

Hi

 

LIMIT 1 would bring back 1 row total. So if there were 100 unique event_tags you would only get the first one returned.

 

All the best

 

Keith