Select fields from table with DISTINCT field

debugcn 에 게시 Dev

Tab

I have a table of this type

| user | parents | sons |

with

parents and sons columuns which can be NULL.
user column can contains duplicated

I write this query:

SELECT user, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd 
  FROM my_table 
  ORDER BY (parentsEd + sonsEd) DESC

and it works! So it give a result with duplicated users. How I can to have DISTINC users by using (parentsEd + sonsEd) DESC ORDER ?

M Khalid Junaid

Use GROUP BY before the order clause and if you want order results first then group them then use subselect

SELECT `user`, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd
 FROM my_table GROUP BY `user` ORDER BY (parentsEd + sonsEd) DESC

SELECT DISTINCT `user`, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd
 FROM my_table  ORDER BY (parentsEd + sonsEd) DESC


SELECT a.* FROM (
SELECT DISTINCT `user`, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd
 FROM my_table  ORDER BY (parentsEd + sonsEd) DESC ) a GROUP BY a.`user`

이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.

침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제

에서 수정2021-05-29

몇 마디 만하겠습니다

0리뷰

로그인참여 후 검토

Related 관련 기사

기사

Select fields from table with DISTINCT field

Select fields from table with DISTINCT field

Select multiple fields from columns with distinct and limit

Select all fields from a table where a field in another table with an ID is equal to a string

How to SELECT distinct with MAX and return full table fields

select distinct from elasticsearch

Replace field with field from other table in SELECT query

Postgresql : select distinct * from func ();

Select rows from DB in dependence of field value in child table

Dynamic table name in where clause using a field from select

쿼리 성능 비교 : Join Vs Select Distinct From Table

Django 모델-SELECT DISTINCT (foo) FROM table이 너무 느립니다.

select * from (select * from table)

How to cross join a table and use fields from the select statment in the where clause of the cross join

SQL Server : SELECT DISTINCT [COL1] from {TABLE} WHERE [COL2] = 'A'AND [COL2] <> 'B'

SQL Server : SELECT DISTINCT [COL1] from {TABLE} WHERE [COL2] = 'A'AND [COL2] <> 'B'

MongoDB 집계 쿼리 대 MySQL SELECT field1 FROM table

SELECT FROM table, ORDER BY IF (...)

Sytax error when select distinct rows in a sql table

select a field values that are not found in another table

SQL query to dynamically COUNT(FIELD) for all fields of table X

How to select all non-distinct rows from a DB?

MySQL Select Distinct values from 2 tables and Count

Select from another select without temporary table?

SELECT array of cols FROM table

RegEx in select from table in db

SELECT * FROM table Where CURDATE ()

CREATE TABLE AS select * from partitioned table

SELECT * INTO [newdatabase]. [table] FROM [otherdatabase]. [table]

PostgreSQL : SELECT DISTINCT 대 SELECT DISTINCT ON (id)

Pull field from another table in MVC