selecting unique values from a column
PhpMysqlSqlPhp Problem Overview
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
Php Solutions
Solution 1 - Php
Use the DISTINCT operator in MySQL:
SELECT DISTINCT(Date) AS Date FROM buy ORDER BY Date DESC;
Solution 2 - Php
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
Solution 3 - Php
Use this query to get values
SELECT * FROM `buy` group by date order by date DESC
Solution 4 - Php
The rest are almost correct, except they should order by Date DESC
SELECT DISTINCT(Date) AS Date FROM buy ORDER BY Date DESC;
Solution 5 - Php
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
Solution 6 - Php
Another DISTINCT answer, but with multiple values:
SELECT DISTINCT `field1`, `field2`, `field3` FROM `some_table` WHERE `some_field` > 5000 ORDER BY `some_field`
Solution 7 - Php
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
Solution 8 - Php
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;
Solution 9 - Php
There is a specific keyword for the achieving the same.
SELECT DISTINCT( Date ) AS Date
FROM buy
ORDER BY Date DESC;