distinct displays unique columns, how to display the lines where they are?

  • 3
    expand the question in more detail. because distinct produces unique sets of column values ​​- which are exactly those rows. Give an example of the data that gives distinct on what data and what you want to receive in response. And most importantly, which sql dialect needs it. I have a feeling that what you want is most easily done by window functions, which, for example, are not in mysql - Mike
  • one
    The question is completely incomprehensible. DISTINCT in SELECT as if discards duplicate data in the query, it does not display anything unique. And what have the columns? Would you at least have a request attached to the question, but what does this mean? - dubok79
  • There is a table: id, name, action. The query select distinct action from table will output only unique data on the action, and I would like all 3 columns. - Mikhail Kolomiets
  • Standard question: what should be about a specific action, what id and what name, if there are several - Mike
  • 2
    what does the first one mean? select distinct gave you unique action. those. one by one action. And to this one action there can be several id. And why the select * from table group by action not suitable. However, if you don’t have mysql, then you should take aggregate functions from all those who are not participating in the group by, for example min (id) for the minimum id for this action - Mike

2 answers 2

Provided that id is unique:

 SELECT id, name, action FROM table WHERE id = ANY( SELECT min(id) FROM table GROUP BY action )
  • What you need =) !!! - Mikhail Kolomiets
  • Effective query bad. There is no need to make a subquery (second reading from table) in this case. The task should be solved and solved easier. - pegoopik
  • SELECT MIN (id) id, MIN (name) name, action FROM table GROUP BY action does not preserve integrity. SELECT id, name, action FROM (SELECT MIN (id) OVER (PARTITION BY action) min_id, * FROM table) T WHERE id = min_id - window functions, in this case, are even more inefficient. especially if there is an index by id (which, I think, is obvious). - bmsdave
  • test (remote postgresql9.4 with a table of 50,000 records) with group by - 35mc, with a window function - 80mc. - bmsdave
 SELECT MIN(id)id, MIN(name)name, action FROM table GROUP BY action

Or if it is necessary for the id to match the name, then:

 SELECT id, name, action FROM( SELECT MIN(id)OVER(PARTITION BY action)min_id, * FROM table )T WHERE id = min_id