Query fails when Distinct is used

Question

When I put DISTINCT on my operator column, my query stops working. When I remove DISTINCT my query works fine.

$resource= $con->query("SELECT date,factory,DISTINCT(operator)as operator,FROM 
Myfactories where factory='$login_session' AND Year='2017' ");

DISTINCT must come first and it doesn't need parentheses. You may want to look into GROUP BY depending on what you are trying to accomplish. `,FROM` looks troublesome too. — mickmackusa, Apr 13 '17 at 03:59
Possible duplicate of http://stackoverflow.com/questions/54418/how-do-i-or-can-i-select-distinct-on-multiple-columns — mickmackusa, Apr 13 '17 at 04:07
This question shows a distinct lack of effort to error check, research, and self-solve. It should be closed as duplicate or off-topic as typo. — mickmackusa, Apr 13 '17 at 04:42
To be honest This question is going to be helpful to so many novices as its simple and straight forward interms of issue and solution . Most faq's online dont even mention on order of operations when it comes to distinct or if they do its a tiny exerpt in a 2000 line blog . In my opinion Its a simple question but that can leave one scratching there head for one to two hours researching . — user3548161, Apr 13 '17 at 04:52

score 2 · Accepted Answer · edited May 23 '17 at 11:54

If running the query directly in your database or using mysqli_error(), you would have been presented with:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(operator) as operator, FROM Myfactories

Which would have definitively confirmed what you more-or-less knew.

Then you would naturally look to MYSQL documentation to see what specifications DISTINCT has by visiting: https://dev.mysql.com/doc/refman/5.7/en/select.html

14.2.9 SELECT Syntax

[ALL | DISTINCT | DISTINCTROW ]
  [HIGH_PRIORITY]
  [STRAIGHT_JOIN]
  [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
  [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references

This means that all of those bracketed keywords must come before any column expressions.

Additional tutorial resources that speak specifically about SELECT DISTINCT on multiple columns include:

Here is one of many, many youtube videos that explain how to use DISTINCT on multiple columns: https://www.youtube.com/watch?v=-C45IpqUbUA

And if any of those non-StackOverflow resources still didn't do it. Stackoverflow offers many duplicate questions that are in some ways better than anything else on the net.

How do I (or can I) SELECT DISTINCT on multiple columns? (A great post because the accepted answer speaks about optimization with GROUP BY)
SELECT DISTINCT on multiple columns along with other columns (A great post because the accepted answer speaks about optimization with GROUP BY)
Using SELECT DISTINCT in MYSQL (speaks about GROUP BY but not for optimization purposes)

Following the correct adjustment of the DISTINCT keyword, you would have been presented with another error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `MyFactories`

The correction of this typo would be to merely remove the , between the factory column and the FROM keyword.

As recommended by other pages on SO, you should use the following for your sql statement:

SELECT `date`,`factory`,`operator` FROM `Myfactories` WHERE `factory`='$login_session' AND `Year`='2017' GROUP BY `date`,`factory`,`operator`

score -1 · Answer 2 · answered Apr 13 '17 at 04:02

-1

Try like this

$change="where factory='$login_session' AND Year='2017'";
$resource= $con->query("SELECT DISTINCT operator, date, factory FROM Myfactories $change")

answered Apr 13 '17 at 04:02

Rikal

215
2
12

1

code-only answers do little to educate. If you are going to poach my comment info, then at least put in the work to explain your answer. (my suspicion is that this answer won't yield the desired resultset anyhow) – mickmackusa Apr 13 '17 at 04:04
1

Thanks guys its working , I overlooked that Distinct must come first – user3548161 Apr 13 '17 at 04:10

Query fails when Distinct is used

2 Answers2