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    lt  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   lt li gt  lt a href  http   www website     row  Date     html  gt buy    date  j  M Y   strtotime  row  Date       lt  a gt  lt  li gt               mysql close  con     gt     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

User · Answer

Use this query to get values  SELECT   FROM  buy  group by date order by date DESC

User · Answer

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

User · Answer

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

User · Answer

Use the DISTINCT operator in MySQL   SELECT DISTINCT Date  AS Date FROM buy ORDER BY Date DESC

User · Answer

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     quot id  quot     quot    product id     quot    quot name  quot     quot    product name     quot          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       quot id quot    quot 1 quot   quot name quot    quot azd quot     quot id quot    quot 2 quot   quot name quot    quot xyz quot    2011-12-11T00 00 00Z       quot id quot    quot 3 quot   quot name quot    quot ase quot     quot id quot    quot 4 quot   quot name quot    quot azwed quot    2011-12-10T00 00 00Z       quot id quot    quot 5 quot   quot name quot    quot wed quot     quot id quot    quot 6 quot   quot name quot    quot cvg quot    2011-12-09T00 00 00Z       quot id quot    quot 7 quot   quot name quot    quot cvig quot     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

User · Answer

Another DISTINCT answer  but with multiple values   SELECT DISTINCT  field1    field2    field3  FROM  some table   WHERE  some field   gt  5000 ORDER BY  some field

User · Answer

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

User · Answer

The rest are almost correct  except they should order by Date DESC  SELECT DISTINCT Date  AS Date FROM buy ORDER BY Date DESC

User · Answer

There is a specific keyword for the achieving the same   SELECT DISTINCT  Date   AS Date  FROM   buy  ORDER  BY Date DESC

[php] selecting unique values from a column

Examples related to php

Examples related to mysql

Examples related to sql