MySQL & PHP query: using SELECT * and DISTINCT
-
Hey CodeProject! So I'm trying to make a query in PHP and mySQL.. One of the database tables has a value I want to SELECT DISTINCT while selecting everything else regularly with an asterisk (*). Example:
$model_select = "select DISTINCT model FROM models where shw=1 ORDER BY modelid"; $model_select = "select * FROM models where shw=1 ORDER BY modelid";Is there a way I can do both DISTINCT and * in the same query command? Or is there a way around this without making 2 queries? It would be great if I could 'cause I'm using the mysql_fetch_array thing. Thanks. -Paul -
Hey CodeProject! So I'm trying to make a query in PHP and mySQL.. One of the database tables has a value I want to SELECT DISTINCT while selecting everything else regularly with an asterisk (*). Example:
$model_select = "select DISTINCT model FROM models where shw=1 ORDER BY modelid"; $model_select = "select * FROM models where shw=1 ORDER BY modelid";Is there a way I can do both DISTINCT and * in the same query command? Or is there a way around this without making 2 queries? It would be great if I could 'cause I'm using the mysql_fetch_array thing. Thanks. -PaulI don't know, however I think it can't be done because the results would be undefined. Assume the MODELS table holds the following data:
field1 field2
1 2
1 3
1 4
5 6
7 8
7 9If you do
"SELECT DISTINCT field1 FROM MODELS"you would get 1, 5, and 7. Now there are three rows with field1=1, which field2 value should contribute to the result? Assuming"SELECT field2, DISTINCT(field1) FROM MODELS"would be accepted, the field2 values would be unpredictable, i.e. the query is ambiguous. Things would make more sense if you would need, and were allowed to do, the following:"SELECT MAX(field2), DISTINCT(field1) FROM MODELS":) PS: this isn't a PHP question, it is a database related question, the MySQL forum would have been a better choice.Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Hey CodeProject! So I'm trying to make a query in PHP and mySQL.. One of the database tables has a value I want to SELECT DISTINCT while selecting everything else regularly with an asterisk (*). Example:
$model_select = "select DISTINCT model FROM models where shw=1 ORDER BY modelid"; $model_select = "select * FROM models where shw=1 ORDER BY modelid";Is there a way I can do both DISTINCT and * in the same query command? Or is there a way around this without making 2 queries? It would be great if I could 'cause I'm using the mysql_fetch_array thing. Thanks. -Paul -
Hey CodeProject! So I'm trying to make a query in PHP and mySQL.. One of the database tables has a value I want to SELECT DISTINCT while selecting everything else regularly with an asterisk (*). Example:
$model_select = "select DISTINCT model FROM models where shw=1 ORDER BY modelid"; $model_select = "select * FROM models where shw=1 ORDER BY modelid";Is there a way I can do both DISTINCT and * in the same query command? Or is there a way around this without making 2 queries? It would be great if I could 'cause I'm using the mysql_fetch_array thing. Thanks. -PaulA sub-select with a GROUP_CONCAT should do it....although I have not test this query. select GROUP_CONCAT((select DISTINCT model FROM models where shw=1 ORDER BY modelid)) as mod_name, * FROM models where shw=1 ORDER BY modelid this would return the subselect as a comma seperated value on each row by a column named "mod_name". I would advise that you do not use the "*" in the other query as this forces mysql to do a secondary query to find and return the column names for the table models, it is better to just set them in the query to improve performance.
Chris J www.redash.org