Clever Geek Handbook

Using different attributes

enter image description here

We can use * to select all the attributes from the table, I am using various and my table has 16 columns. How can I use different with it. I can't do select distinct Id,* from abc;

Which would be best. Another way could be to select separate ids, col1, col2, etc.

+3


source to share


4 answers


If you want in the results, one line at a time id

, you can use GROUP BY id

. But then it is not recommended to use other columns in the list SELECT

(even if MySQL allows it) - it depends if you have the parameter ANSI

On

or Off

). It is advisable to use other columns with aggregate functions, such as MIN()

, MAX()

, COUNT()

, etc. There is also an aggregate function in MySQL GROUP_CONCAT()

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 ;

Request:

SELECT *
FROM abc
GROUP BY id ;

invalid SQL (up to 92) because you have non-aggregated results in the list SELECT

and are valid in SQL (2003+). However, it is not valid here because the other columns are functionally independent of the grouping column ( id

). MySQL, unfortunately, allows such queries and does not test for functional dependencies.



So, you never know which string (from the number with the same id

) will be returned, or even if - horror! - you get results from different rows (with the same id). As @Andriy points out, the implications are that values ​​for columns other than id

will be chosen arbitrarily. If you want predictable results, just don't use this technique.

Example solution: if you only want one row from each id

and you have a datetime or timestamp (or whatever) column that you can use to order, 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 combination is (id, some_column)

unique.

+4


source


use group instead of separate

group by col1, col2,col3


makes it as separate

+3


source 


SELECT DISTINCT * FROM `some_table`

Absolutely correct syntax.

The error is caused by what you are calling Id, *

. Well *

also includes an ID column, which is usually unique.

So what you need in your case is simply:

SELECT DISTINCT * FROM `abc`
0


source 


    SELECT * FROM abc where id in(select distinct id from abc);

You can do it completely.
Hope it helps


I originally thought it would work for the band for the best. This is the same as for select * froom abc. Sorry guys,

-2


source






More articles:


All Articles