top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to get distinct column values in MySql?

+1 vote
693 views
posted Dec 21, 2016 by Atindra Kumar Nath

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button
Eg: SELECT  DISTINCT agent_code,ord_amount  
FROM orders   
WHERE agent_code='A002';
Can we do without using distinct keywords
Yes Atindra we can but my point of view and performance wise Distinct is best approach here is the sample for that instead of Distinct keyword use GroupBy
SELECT t1.product, COUNT(*)
FROM MyTable AS t1
JOIN (SELECT product, consumer FROM MyTable GROUP BY product, consumer)
    AS t2 ON t1.product = t2.product
GROUP BY t1.product
Yes that what I was looking in the answer
Thanks for helping

2 Answers

+1 vote
 
Best answer

EG: SELECT t1.product, COUNT(*)
FROM MyTable AS t1
JOIN (SELECT product, consumer FROM MyTable GROUP BY product, consumer)
AS t2 ON t1.product = t2.product
GROUP BY t1.product

answer Dec 26, 2016 by Manikandan J
0 votes

HI.. You can try like this without using DISTINCT keyword

SELECT t1.product, COUNT(*) FROM TableName AS t1
JOIN(SELECT product, consumer FROM TableName GROUP BY product,consumer)
AS t2 ON t1.product = t2.product
GROUP BY t1.product
answer Nov 13, 2019 by Siddhi Patel
Similar Questions
0 votes

I have a mysql query that would not execute and I would like to display information about why that's happening.

$myQuery= $mysqli->query("UPDATE table SET id = 1 WHERE id = 3");
if(!$myQuery) //If query couldnt be executed
{
echo $mysqli->error; //Display information about why wasnt executed (eg. Error: couldnt find table)
}
+2 votes

how we can retrieve the unique values for the in a table without using the DISTINCT keyword

+1 vote

I have a table in Mysql DB like following

mysql> describe APPTBL;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| APP_ID      | int(11)     | NO   | PRI | NULL    | auto_increment |
| CREATE_DATE | date        | YES  |     | NULL    |                |
| CLOSE_DATE  | date        | YES  |     | NULL    |                |
| ACTION      | text        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

While Starting of App, i am inserting the app details with CREATE_DATE and when it is closing, i am updating its CLOSE_DATE. But in case if user is restarting the app, i want to update NULL in place of CLOSE_DATE column. Here is my query,

mysql> update APPTBL set CLOSE_DATE = NULL where APP_ID = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> select * from APPTBL ;
+-----------+-------------+------------+------------+
|   APP_ID  | CREATE_DATE | CLOSE_DATE |   ACTION   |
+-----------+-------------+------------+------------+
|    1      |  2013-10-08 | NULL       | XXXXXYYYYY |
+-----------+-------------+------------+------------+
1 row in set (0.00 sec)

But why i am not getting any result in this??

mysql> select * from APPTBL where CLOSE_DATE = NULL;
Empty set (0.00 sec)

Also, as i am writhing a java program using this where

rs = st.executeQuery("update APPTBL set CLOSE_DATE = NULL where APP_ID = 1");
if(!rs.wasNull()){
   // Why it is coming here??
}
...