Solved

Mysql GROUP BY, how to detect when new "group"?

Posted on 2015-02-19
4
71 Views
Last Modified: 2015-03-04
Hi,

When using "SELECT * FROM table GROUP BY city"

What is the best method to detect a new group/city?
I need to post a new html-table and list all related database records associated to each city.

Any suggestions?
0
Comment
Question by:kgp43
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40619363
Your SQL won't do what you think it will - it will simply return one record for each City - because you're grouping your records by City. The rest of the data shown for a record is likely to be from the first record in that City's group.

You may be better off writing your App to select the Distinct Cities, and then loop through that list, firing off another query to select records that match that city.
0
 

Author Comment

by:kgp43
ID: 40619396
You're right.
Think i'm going to use ORDER BY and then check if new city is different from previous one, if so: post table start and ending.

Think that will work?
0
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 40619433
It will, but it doesn't feel like a clean way of doing it. Any reason to not do it the way I suggested. It's actually pretty easy. Here's a very quick demo using PDO:

<?php
$stmt = $dbh->prepare("SELECT * FROM table WHERE city = ?");

foreach ($dbh->query("SELECT DISTINCT city FROM table;") as $row):
	printf("<h1>City: %s</h1>", $row->city);
	
	echo "<table>";
	
	$stmt->execute( array($row->city) );
	while ($row = $stmt->fetch()):
		printf("<tr><td>%s</td><td>%s</td></tr>", $row->column1, $row->column2);
	endwhile;
	
	echo "</table>";
	
endforeach;
?>

Open in new window


Basically it selects a distinct list of cities, and loops through them. For each city it finds it pulls the matching records from your database, and displays a table containing the data.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40620621
"SELECT * FROM table GROUP BY city" is bad for 2 reasons (at least)

1. that * will produce changes that your downstream code may not be prepared for, it is a bug creator 'par excellence'

2. MySQL has this bizarre "feature" that allows this lazy syntax, and it "approximates" values for the fields (read: guesses)

In most flavours of SQL you MUST specify ALL the fields that will form a group, then ALL OTHER fields must be handled by an aggregate function like MAX() MIN() SUM() COUNT() etc.

The default settings of MySQL ignore this convention; and don't require the more stringent syntax. One risk is (if someone does alter the settings) and the system all of a sudden will require precise syntax and all previous lazy syntax grouping queries will fail. The other, perhaps more disturbing, risk is that the data in all the non-aggregated columns just isn't accurate and should be relied upon (unless you do enjoy "indeterminate" results).

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.
http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html (emphasis added)

Use these "lazy group bys" with caution IMHO
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question