problem combining DESC and DISTINCT (or GROUP BY)

Square1

Hi all:

Interesting problem.

A table has a number of names in and an accompanying ID #. It is not unique. So it may look like (descending):

5 Jim Jones
4 Tom Smith
3 Carol Ryan
2 Jim Jones
1 Gail Reese

I want a query to provide a drop down in DISTINCT Name DESC ID order. You may think that would be:

5 Jim Jones
4 Tom Smith
3 Carol Ryan
1 Gail Reese

But it isn't. It is coming up:

4 Tom Smith
3 Carol Ryan
2 Jim Jones
1 Gail Reese

Even though it is in DESC order of ID the oldest UNIQUE name is the one getting read.

Here is the code:

$queryPlayer = "
									SELECT
										player
									FROM
										Events
									WHERE
										listingDate = CURDATE() AND
										LENGTH(tip) > '0'
									GROUP BY
										player
									ORDER BY
										EventsID DESC,
  										CASE
											WHEN LEFT(player, 1) = ''''
										THEN
											SUBSTRING(player, 2)
										ELSE
											player
										END

bradgrafelman

I get the 1st set of results as expected when I tried your same query sans the WHERE clause.

Thus, I would say your problem is that the (5, 'Jim Jones') row doesn't match one or both of the conditions in your WHERE clause.

Square1

I commented out the whole WHERE section. I also made it so the player and the ID is displayed in the drop down. Although I used an actual DB query, the result is still the same. The earliest instance of a UNIQUE record is the one being compiled, not the most recent, even though DESC is chosen.

bradgrafelman

Can you show us your table schema and some sample rows?

Like I said, I tested your query without the WHERE clause after INSERT'ing the 5 records you showed in the first group of rows, and I received the second group of rows as expected.

cretaceous

Seems to me it's a case where mySql will give unpredictable results as it is grouping on the person name, but the id it shows could be either 5 or 2 depending on the weather.
The ordering has no effect on which ID of the two (or more) matches it shows.

You're after an 'order by' as a sub clause of the 'Group by' - sorry, I don't know how to write that

Derokorian

cretaceous;10999612 wrote:
Seems to me it's a case where mySql will give unpredictable results as it is grouping on the person name, but the id it shows could be either 5 or 2 depending on the weather.
The ordering has no effect on which ID of the two (or more) matches it shows.

You're after an 'order by' as a sub clause of the 'Group by' - sorry, I don't know how to write that

SELECT MAX(id),player ... GROUP BY player

Square1

cretaceous;10999612 wrote:
Seems to me it's a case where mySql will give unpredictable results as it is grouping on the person name, but the id it shows could be either 5 or 2 depending on the weather.
The ordering has no effect on which ID of the two (or more) matches it shows.

You're after an 'order by' as a sub clause of the 'Group by' - sorry, I don't know how to write that

It is not actually switching between 5 or 2 for me. It is consistently the earlier record (2).

I also tried using DISTINCT instead of GROUP BY with the same results.

Derokorian

Square1;10999615 wrote:
It is not actually switching between 5 or 2 for me. It is consistently the earlier record (2).

I also tried using DISTINCT instead of GROUP BY with the same results.

This is because MySQL searches from the beginning of the table, taking each unique name it finds as a result row, then orders those results by their IDs instead of first ordering by ID and then searching.

Weedpacket

Square1 wrote:
cretaceous wrote:
Seems to me it's a case where mySql will give unpredictable results as it is grouping on the person name, but the id it shows could be either 5 or 2 depending on the weather.
It is not actually switching between 5 or 2 for me. It is consistently the earlier record (2).

That's because the DBMS just happens to come across the record with an ID of "2" before it comes across the record with an ID of "5". After a regular maintenance round of vacuuming and reindexing, the records might be rearranged so that the record with an ID of "5" appears first in the new version of the table.

Or it might not. And if it did, it might change again at the next maintenance. Whether it does or not depends on what indexes are defined and the algorithmic details of how they're implemented - the idea being to arrange the records in the table so that searching it is as fast as possible.

The order of rows as they are physically listed in the table is not fixed (clustered indexes notwithstanding, yeah I know; but if you know about clustered indexes you already know the rest of this stuff); if you want the records arranged in a particular order you have specify it in the query. Otherwise the DBMS is free to present them in whatever order takes its fancy.

bradgrafelman

cretaceous;10999612 wrote:
the id it shows could be either 5 or 2 depending on the weather.

Not at all - that's what the ORDER BY clause is for.

cretaceous;10999612 wrote:
The ordering has no effect on which ID of the two (or more) matches it shows.

Of course it has an effect. Why else would MySQL even have an 'ORDER BY' clause if it didn't have any effect on the order of the results?

cretaceous;10999612 wrote:
You're after an 'order by' as a sub clause of the 'Group by' - sorry, I don't know how to write that

Me neither, because such a thing doesn't exist. You just need a normal 'ORDER BY' clause.

Derokorian;10999616 wrote:
This is because MySQL searches from the beginning of the table

... except that tables don't really have a "beginning." If you don't have an ORDER BY clause, the order of the rows returned is undefined/ambiguous.

@: Rather than selecting the EventsID column directly, try SELECT'ing something like:

GROUP_CONCAT(EventsID) ListOfIDs

and let us know what you get.

Also, you still haven't shown us the schema and sample data I asked for above. I'm unable to reproduce your results, but that was with my own schema and my own data (both created based on your simplified description in your first post).

EDIT: Looks like Weedpacket replied while I was composing my own reply above. At least we agree on the ambiguous order of rows returned without an 'ORDER BY' clause. :p

cretaceous

Brad that's plain wrong !
Weedpacket is correct.

or does PostgreSql handle this better which I believe is what you use Brad?

Weedpacket

Another thing that's wrong is MySQL.

Any other RDBMS would refuse to run that query because it doesn't make sense (Order these players by EventID? Which EventID? This player has three EventIDs - which one am I supposed to use?)

Derokorian

Weedpacket;10999640 wrote:
Another thing that's wrong is MySQL.

Any other RDBMS would refuse to run that query because it doesn't make sense (Order these players by EventID? Which EventID? This player has three EventIDs - which one am I supposed to use?)

Why is why, according to proper SQL standards any column selected not part of the group by needs to be in an aggregate function (such as MIN, MAX, AVG, etc)

Square1

bradgrafelman;10999606 wrote:
Can you show us your table schema and some sample rows?

Trying the following to show schema. What am I doing incorreectly?

	$query = "SHOW CREATE TABLE FROM Events";

Square1

Tried MAX() without success. Wrapping MAX around ID caused it not to display. Here is some test code:

        					<?php
								$queryPromoter = "
									SELECT
										promoter, MAX(EventsID) 
									FROM
										Events
									WHERE
										listingDate = CURDATE() AND
										LENGTH(tip) > '0'
									GROUP BY
										promoter	
									ORDER BY
										EventsID DESC
									";

							$resultPromoter = mysqli_query($link, $queryPromoter);

							echo "<select name='promoter' size='1'>";
								while($rowPromoter = mysqli_fetch_array($resultPromoter, MYSQLI_ASSOC)){
									echo "<option>".htmlentities($rowPromoter["promoter"])." | ".htmlentities($rowPromoter["EventsID"])."</option>";
								}
							echo "</select>";

							mysqli_free_result($resultPromoter);
						?>

$rowPromoter["promoter"] appears in the drop down. When I add MAX $rowPromoter["EventsID"] does not appear.

bradgrafelman

Square1;10999664 wrote:
Trying the following to show schema. What am I doing incorreectly?
	$query = "SHOW CREATE TABLE FROM Events";

The MySQL manual page for SHOW CREATE TABLE should help with that.

Square1;10999666 wrote:
Wrapping MAX around ID caused it not to display.

Did you also change the code that displays the information since you're no longer SELECT'ing the EventsID column?

Square1;10999666 wrote:
When I add MAX $rowPromoter["EventsID"] does not appear.

... which should be expected. After all, you're not SELECT'ing EventsID, so why should $rowPromoter['EventsID'] exist? You need to change the associative array index to match what you're actually SELECT'ing (which can be shortened a bit if you use an alias, e.g. 'SELECT MAX(colName) MaxCol').

Derokorian

Square1;10999666 wrote:

Tried MAX() without success. Wrapping MAX around ID caused it not to display. Here is some test code:

        					<?php
								$queryPromoter = "
									SELECT
										promoter, MAX(EventsID) 
									FROM
										Events
									WHERE
										listingDate = CURDATE() AND
										LENGTH(tip) > '0'
									GROUP BY
										promoter	
									ORDER BY
										EventsID DESC
									";

							$resultPromoter = mysqli_query($link, $queryPromoter);

							echo "<select name='promoter' size='1'>";
								while($rowPromoter = mysqli_fetch_array($resultPromoter, MYSQLI_ASSOC)){
									echo "<option>".htmlentities($rowPromoter["promoter"])." | ".htmlentities($rowPromoter["EventsID"])."</option>";
								}
							echo "</select>";

							mysqli_free_result($resultPromoter);
						?>

$rowPromoter["promoter"] appears in the drop down. When I add MAX $rowPromoter["EventsID"] does not appear.

Use an alias like MAX(EventsID) as EventsID and it will work again, otherwise the column name ends up being MAX(EventsID) such that $rowPromoter["MAX(EventsID)"] would be how you access it without the alias.

Edit: Didn't see page two - this is what brad said.

Square1

MAX(EventsID) as EventsID solved it. Thank you!

As for SHOW CREATE TABLES (SHOW CREATE TABLE tbl_name) how do I get it to display? print_r?

johanafm

It's the same as issuing any other query. You fetch the data from a result set resource into a variable. The type and contents of that variable will vary depending on what you select and how you fetch it from the result set.

One thing that never does change though, is [man]var_dump[/man]: It will always tell you the type and contents of any variable. Thus it can be used even if you don't know what that type is and so you can find out on your own instead of asking without trying.