selecting unique values from a column

Question

I have a MySQL table which contains the following type of information:

    Date            product 
2011-12-12           azd
2011-12-12           yxm
2011-12-10           sdx
2011-12-10           ssdd

Here is an example of a script I use to get data from this table:

<?php

$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("db", $con);
$sql=mysql_query("SELECT * FROM buy ORDER BY Date");
while($row = mysql_fetch_array($sql))
{

 echo "<li><a href='http://www.website/". $row['Date'].".html'>buy ". date("j, M Y", strtotime($row["Date"]))."</a></li>";

    }
    mysql_close($con);
?>

This script displays every date from the table, e.g.

12.dec 2011
12.dec.2011
10.dec.2011
10.dec.2011

I would like to only display unique dates, e.g.

12.dec.2011
10.dec.2011

Possible duplicate of [MySQL How to Return Unique/Distinct Results?](http://stackoverflow.com/questions/4663181/mysql-how-to-return-unique-distinct-results) — Jim Fell, Jul 12 '16 at 14:17
Not really sure the proper etiquette for this, but I have a need for something that is similar to this. I have a list of the values like those above displayed as a list of all the dates. I want to add some extra space between the end of one date sequence and the start of another. Thoughts on this? — D.j. Dimick, Mar 16 '21 at 23:32

score 361 · Accepted Answer · edited May 14 '15 at 06:44

361

Use the DISTINCT operator in MySQL:

SELECT DISTINCT(Date) AS Date FROM buy ORDER BY Date DESC;

edited May 14 '15 at 06:44

Narendrasingh Sisodia

21,247
6
47
54

answered Dec 20 '11 at 07:10

Léon Rodenburg

4,894
1
18
18

18

The unfortunate problem of `DISTINCT` is that it only returns that one field... so if you want the whole record, the DISTINCT is worthless (unless it is an id field, and you can do a second query where id IN that list). Good news, though, if you have multiple duplicate results as a result of a JOIN you can do a GROUP BY and get the full results filtered. – Chadwick Meyer Dec 10 '15 at 19:55
2

That is not correct Chadwick Meyer ! You can chose multiple fields, but you have to group them. You can make it like this: SELECT DISTINCT(name) AS yourName, id AS yourId FROM table GROUP BY name ORDER BY name . You'll be surprised ! – Lucian Minea May 05 '19 at 10:36

score 44 · Answer 2 · edited Sep 26 '19 at 15:52

44

use

SELECT DISTINCT Date FROM buy ORDER BY Date

so MySQL removes duplicates

BTW: using explicit column names in SELECT uses less resources in PHP when you're getting a large result from MySQL

edited Sep 26 '19 at 15:52

Gerard de Visser

7,590
9
50
58

answered Dec 20 '11 at 07:10

rabudde

7,498
6
53
91

1

can you explain "using explicit column names" ? which way is less resource taking and which one is high? with example a bit please? – Nabeel Khan Mar 30 '16 at 10:07
Common use case is to "stream" data from database into multidimensional array in PHP; so your PHP process simply wastes memory with data you might not need. For only some datasets this is negligible, but when handling thousands of rows, this could make a difference. – rabudde Mar 30 '16 at 15:38
4

@NabeelKhan instead of using SELECT * ... use SELECT column 1, column 2... Unless you really do need all columns. – LPChip Nov 18 '17 at 20:43
@LPChip using tablename.columnname is beneficial over only columnname? – Nabeel Khan Nov 22 '17 at 09:18
2

@NabeelKhan you only need tablename.columnname when you join tables. – LPChip Nov 22 '17 at 14:11
@LPChip i understand, my question is that if we do that in normal queries, is it any faster? or it gives error? – Nabeel Khan Nov 26 '17 at 11:53

score 26 · Answer 3 · edited Aug 24 '14 at 18:47

26

Use this query to get values

SELECT * FROM `buy` group by date order by date DESC

edited Aug 24 '14 at 18:47

Brad Larson

170,088
45
397
571

answered Feb 04 '14 at 04:46

John

723
1
10
12

3

I found that this was not helpful. Say you have 5 items, all with the same `date` but different `description`. Using the above command _will_ show distinct `date`s, but only the `description` of the first item found. – onebree Apr 21 '15 at 15:58
@onebree - in that case, you can do `GROUP BY date,description`. – ToolmakerSteve Oct 07 '19 at 11:35

score 19 · Answer 4 · answered Dec 20 '11 at 07:18

19

The rest are almost correct, except they should order by Date DESC

SELECT DISTINCT(Date) AS Date FROM buy ORDER BY Date DESC;

answered Dec 20 '11 at 07:18

ajreal

46,720
11
89
119

score 5 · Answer 5 · answered Apr 07 '16 at 05:36

5

DISTINCT is always a right choice to get unique values. Also you can do it alternatively without using it. That's GROUP BY. Which has simply add at the end of the query and followed by the column name.

SELECT * FROM buy GROUP BY date,description

answered Apr 07 '16 at 05:36

Kvvaradha

732
1
13
28

Pedro Lobito · Answer 6 · 2017-11-22T12:37:25.717

4

Another DISTINCT answer, but with multiple values:

SELECT DISTINCT `field1`, `field2`, `field3` FROM `some_table`  WHERE `some_field` > 5000 ORDER BY `some_field`

edited Nov 22 '17 at 12:37

answered Sep 28 '17 at 18:09

Pedro Lobito

94,083
31
258
268

Perhaps explain how this technique (which makes no attempt to relate to the OP's question) behaves so that researchers can decide whether it will work as they need it to. – mickmackusa Oct 28 '20 at 08:14
@mickmackusa - As stated on the description, there's nothing special about this answer, it just demonstrates how to use multiple `DISTINCT` fields on a `SELECT` query. – Pedro Lobito Oct 28 '20 at 17:08
1

So does it ensure that the column immediately following the keyword is unique and the remaining columns may or may not be unique? Or does it ensure that the combination of all columns are unique in the result set?This is what researchers who don't understand the syntax might ask themselves. This is why you should explain your answer more generously. You could choose to argue with me that your post is "good enough", but that will be a futile waste of time. My honest opinion is that this answer is probably better transferred to a question that is asking for a resultset with more than one column. – mickmackusa Oct 28 '20 at 22:38
I'm sorry, but I've no time to reply. Down vote if you may, which is normally the way to disagree with an answer. Good luck. – Pedro Lobito Oct 29 '20 at 20:51
I will only DV wrong answers and answers that misinform because they "do harm". This answer is simply not informing. – mickmackusa Oct 29 '20 at 22:03

score 2 · Answer 7 · 2020-10-28T08:54:26.890

Use something like this in case you also want to output products details per date as JSON and the MySQL version does not support JSON functions.

SELECT `date`,
CONCAT('{',GROUP_CONCAT('{\"id\": \"',`product_id`,'\",\"name\": \"',`product_name`,'\"}'),'}') as `productsJSON`
FROM `buy` group by `date` 
order by `date` DESC

 product_id product_name     date  
|    1     |     azd    | 2011-12-12 |
|    2     |     xyz    | 2011-12-12 |
|    3     |     ase    | 2011-12-11 |
|    4     |     azwed  | 2011-12-11 |
|    5     |     wed    | 2011-12-10 |
|    6     |     cvg    | 2011-12-10 |
|    7     |     cvig   | 2011-12-09 |

RESULT
       date                                productsJSON
2011-12-12T00:00:00Z    {{"id": "1","name": "azd"},{"id": "2","name": "xyz"}}
2011-12-11T00:00:00Z    {{"id": "3","name": "ase"},{"id": "4","name": "azwed"}}
2011-12-10T00:00:00Z    {{"id": "5","name": "wed"},{"id": "6","name": "cvg"}}
2011-12-09T00:00:00Z    {{"id": "7","name": "cvig"}}

Try it out in SQL Fiddle

If you are using a MySQL version that supports JSON functions then the above query could be re-written:

SELECT `date`,JSON_OBJECTAGG(CONCAT('product-',`product_id`),JSON_OBJECT('id', `product_id`, 'name', `product_name`)) as `productsJSON`
FROM `buy` group by `date`
order by `date` DESC;

Try both in DB Fiddle

Yikes! Please do not encourage manually construct json strings via aggregate and string function in sql -- it is an inappropriate tool that is vulnerable to breakage depending on the data. Even if it works in this case, there will be a cleaner and easier to maintain way to do this outside of sql. — mickmackusa, Oct 28 '20 at 08:17
@mickmackusa if you are using a version that does not support natively `JSON` functions this is something that you could do. In addition the problem of breaking data is solvable. In the above case the field that needs attention is `product_name`. Thanks for your comment. Long time i heard **Yikes**. — , Oct 28 '20 at 09:02

score 1 · Answer 8 · edited Nov 29 '17 at 16:39

1

Depends on what you need.

In this case I suggest:

SELECT DISTINCT(Date) AS Date FROM buy ORDER BY Date DESC;

because there are few fields and the execution time of DISTINCT is lower than the execution of GROUP BY.

In other cases, for example where there are many fields, I prefer:

SELECT * FROM buy GROUP BY date ORDER BY date DESC;

edited Nov 29 '17 at 16:39

Nander Speerstra

1,496
6
24
29

answered Nov 29 '17 at 14:47

Marco Desposito

11
1

score 0 · Answer 9 · edited Nov 29 '17 at 15:41

0

There is a specific keyword for the achieving the same.

SELECT DISTINCT( Date ) AS Date 
FROM   buy 
ORDER  BY Date DESC;

edited Nov 29 '17 at 15:41

Nick

1,178
3
24
36

answered Nov 29 '17 at 15:05

Ashutosh dwivedi

510
3
16

selecting unique values from a column

9 Answers9

Linked

Related