[mysql] Mysql select distinct

I am trying to select of the duplicate rows in mysql table it's working fine for me but the problem is that it is not letting me select all the fields in that query , just letting me select the field name i used as distinct , lemme write the query for better understading

mysql_query("SELECT DISTINCT ticket_id FROM temp_tickets ORDER BY ticket_id")

mysql_query("SELECT * , DISTINCT ticket_id FROM temp_tickets ORDER BY ticket_id")

1st one is working fine

now when i am trying to select all fields i am ending up with errors

i am trying to select the latest of the duplicates let say ticket_id 127 is 3 times on row id 7,8,9 so i want to select it once with the latest entry that would be 9 in this case and this applies on all the rest of the ticket_id's

Any idea thanks

This question is related to mysql select greatest-n-per-group

The answer is

DISTINCT is not a function that applies only to some columns. It's a query modifier that applies to all columns in the select-list.

That is, DISTINCT reduces rows only if all columns are identical to the columns of another row.

DISTINCT must follow immediately after SELECT (along with other query modifiers, like SQL_CALC_FOUND_ROWS). Then following the query modifiers, you can list columns.

  • RIGHT: SELECT DISTINCT foo, ticket_id FROM table...

    Output a row for each distinct pairing of values across ticket_id and foo.

  • WRONG: SELECT foo, DISTINCT ticket_id FROM table...

    If there are three distinct values of ticket_id, would this return only three rows? What if there are six distinct values of foo? Which three values of the six possible values of foo should be output?
    It's ambiguous as written.

You can use group by instead of distinct. Because when you use distinct, you'll get struggle to select all values from table. Unlike when you use group by, you can get distinct values and also all fields in table.

You can use DISTINCT like that

mysql_query("SELECT DISTINCT(ticket_id), column1, column2, column3 
FROM temp_tickets 
ORDER BY ticket_id");

Questions with mysql tag:

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client php mysqli_connect: authentication method unknown to the client [caching_sha2_password] phpMyAdmin on MySQL 8.0 Authentication plugin 'caching_sha2_password' cannot be loaded Error loading MySQLdb Module 'Did you install mysqlclient or MySQL-python?' select rows in sql with latest date for each ID repeated multiple times How to find MySQL process list and to kill those processes? Access denied; you need (at least one of) the SUPER privilege(s) for this operation Import data.sql MySQL Docker Container PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers Hibernate Error executing DDL via JDBC Statement Your password does not satisfy the current policy requirements MySql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Laravel: PDOException: could not find driver Default password of mysql in ubuntu server 16.04 #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ Job for mysqld.service failed See "systemctl status mysqld.service" Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by MySQL Error: : 'Access denied for user 'root'@'localhost' Unable to start the mysql server in ubuntu How to turn on/off MySQL strict mode in localhost (xampp)? How to store Emoji Character in MySQL Database ERROR 1698 (28000): Access denied for user 'root'@'localhost' What is the meaning of <> in mysql query? The Response content must be a string or object implementing __toString(), "boolean" given after move to psql Xampp-mysql - "Table doesn't exist in engine" #1932 #1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by MySQL fails on: mysql "ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded" How to insert TIMESTAMP into my MySQL table? How to create a foreign key in phpmyadmin JPA Hibernate Persistence exception [PersistenceUnit: default] Unable to build Hibernate SessionFactory PHP: Inserting Values from the Form into MySQL #1292 - Incorrect date value: '0000-00-00' WooCommerce: Finding the products in database ERROR 1067 (42000): Invalid default value for 'created_at' SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost' SQL query to check if a name begins and ends with a vowel MySQL: When is Flush Privileges in MySQL really needed? Error in MySQL when setting default value for DATE or DATETIME

Questions with select tag:

Warning: Use the 'defaultValue' or 'value' props on <select> instead of setting 'selected' on <option> SQL query to check if a name begins and ends with a vowel Angular2 *ngFor in select list, set active based on string from object SQL: Two select statements in one query How to get selected value of a dropdown menu in ReactJS DATEDIFF function in Oracle How to filter an array of objects based on values in an inner array with jq? Select unique values with 'select' function in 'dplyr' library how to set select element as readonly ('disabled' doesnt pass select value on server) Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working How to remove all options from a dropdown using jQuery / JavaScript How do I change the select box arrow SQL query for getting data for last 3 months SELECT using 'CASE' in SQL Get records of current month ORA-01843 not a valid month- Comparing Dates Proper way of checking if row exists in table in PL/SQL block How does Subquery in select statement work in oracle select from one table, insert into another table oracle sql query ORA-01830: date format picture ends before converting entire input string / Select sum where date query Convert INT to VARCHAR SQL MySQL Nested Select Query? comma separated string of selected values in mysql Find all stored procedures that reference a specific column in some table SQL use CASE statement in WHERE IN clause Get Selected value from dropdown using JavaScript Select objects based on value of variable in object using jq How to get a particular date format ('dd-MMM-yyyy') in SELECT query SQL Server 2008 R2 Fastest way to determine if record exists how to dynamically add options to an existing select in vanilla javascript How to change the text color of first select option How to write a SQL DELETE statement with a SELECT statement in the WHERE clause? Return Bit Value as 1/0 and NOT True/False in SQL Server SQL How to replace values of select return? MySQL select query with multiple conditions SQL Server convert select a column and convert it to a string Using SELECT result in another SELECT Oracle Date datatype, transformed to 'YYYY-MM-DD HH24:MI:SS TMZ' through SQL MySQL error 1241: Operand should contain 1 column(s) If statement in select (ORACLE) MySQL INSERT INTO ... VALUES and SELECT Select 50 items from list at random to write to file Grant SELECT on multiple tables oracle How to add results of two select commands in same query MySQL Select Query - Get only first 10 characters of a value SELECT CASE WHEN THEN (SELECT) MySQL - sum column value(s) based on row from the same table SQL SELECT WHERE field contains words TSQL CASE with if comparison in SELECT statement Select arrow style change

Questions with greatest-n-per-group tag:

How to select the rows with maximum values in each group with dplyr? MAX function in where clause mysql Pandas get topmost n records within each group SQL Left Join first match only Select info from table where row has max date FORCE INDEX in MySQL - where do I put it? GROUP BY having MAX date How can I select rows with most recent timestamp for each key value? Select row with most recent date per user How to select id with max date group by category in PostgreSQL? SQL query to select distinct row with minimum value How to select data where a field has a min value in MySQL? Get top n records for each group of grouped results Get records with max value for each group of grouped SQL results Select a Column in SQL not in Group By How to get the latest record in each group using GROUP BY? Using ORDER BY and GROUP BY together Get most recent row for given ID Get the latest date from grouped MySQL data SQL select only rows with max value on a column Mysql select distinct Get top 1 row of each group Aggregate a dataframe on a given column and display another column how to sort order of LEFT JOIN in SQL query? Select first row in each GROUP BY group? GROUP BY with MAX(DATE) How do I do top 1 in Oracle? how do I query sql for a latest record date for each user Using LIMIT within GROUP BY to get N results per group? SQL join: selecting the last records in a one-to-many relationship ROW_NUMBER() in MySQL Retrieving the last record in each group - MySQL SQL query to get most recent row for each instance of a given key How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL? Select top 10 records for each category Fetch the row which has the Max value for a column