Where do I put group by clause in this sql?

Databases
1

Hi Guys!

Not sure where to put the GROUP BY clause in this SQL. Can anyone help?


select * from uk_postcode_towns where town like '%madi%'
order by case
when town like 'madi%' then 0
when town like '% %madi% %' then 1
when town like '%madi' then 2
else 3 end, town
2

Nowhere :slight_smile: You don’t have any group by functions (MIN, MAX, etc) in your query, why would you want to group?

Anyway, the GROUP BY goes after the WHERE and before the ORDER BY. I’m sure the mysql reference manual has this bit of info.

3

I have a problem in that if I enter “Madison” into the query, it should retrurn all places with the name “Madison” in them. Currently this query is only returning the first exact match of Madison. Do you know what I need to change in the query to return all Madison matches? I thought a group by was the answer, but maybe not :slight_smile:

4

can we see the query where you tried this?

because if it’s the same as the query you posted, then something else is wrong

5

The query I tried is:

select * from uk_postcode_towns where town like ‘%madison%’
order by case
when town like ‘madi%’ then 0
when town like ‘% %madi% %’ then 1
when town like ‘%madi’ then 2
else 3 end, town

6

that query should work as intended, to return all rows that have madison somewhere in the town name

did you test it outside of php? maybe your php code is only showing the first one