using distinct with all attributes

Question

We can use * to select all attribute from table ,I am using distinct and my table contain 16 columns, How can I use distinct with it.I cannot do select distinct Id,* from abc; What would be the best way. Another way could be select distinct id,col1,col2 etc.

Answer 1

If you want in the results, one row per id, you can use GROUP BY id. But then, it's not advisable to use the other columns in the SELECT list (even if MySQL allows it - that depends on whether you have ANSI setting On or Off). It's advisable to use the other columns with aggregate functions like MIN(), MAX(), COUNT(), etc. In MySQL, there is also a GROUP_CONCAT() aggregate function that will collect all values from a column for a group:

SELECT
    id
  , COUNT(*)   AS number_of_rows_with_same_id
  , MIN(col1)  AS min_col1
  , MAX(col1)  AS max_col1
  --
  , GROUP_CONCAT(col1)  AS gc_col1
  , GROUP_CONCAT(col2)  AS gc_col2
  --
  , GROUP_CONCAT(col16) AS gc_col16
FROM
    abc
GROUP BY
    id ;

---

The query:

SELECT *
FROM abc
GROUP BY id ;

is not valid SQL (up to 92) because you have non-aggregated results in the SELECT list and valid in SQL (2003+). Still, it's invalid here because the other columns are not functionally dependent on the grouping column (id). MySQL unfortunately allows such queries and does no checking of functional dependency.

So, you never know which row (of the many with same id) will be returned or even if - horror! - you get results from different rows (with same id). As @Andriy comments, the consequences are that values for columns other than id will be chosen arbitrarily. If you want predictable results, just don't use such a technique.

---

An example solution: If you want just one row from every id, and you have a datetime or timestamp (or some other) column that you can use for ordering, you can do this:

SELECT t.*
FROM abc AS t
  JOIN
    ( SELECT id
           , MIN(some_column) AS m            -- or MAX()
      FROM abc
      GROUP BY id
    ) AS g
    ON  g.id = t.id
    AND g.m = t.some_column ;

This will work as long as the (id, some_column) combination is unique.

Answer 2

use group by instead of distinct

group by col1, col2,col3

its doing like distinct