SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,045
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Where do I put group by clause in this sql?

    Hi Guys!

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

    Code:
    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,406
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Nowhere 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. #3
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,045
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Nowhere 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.
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Zaggs View Post
    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.
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,045
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •