SQL query how?

Question

SQL

There is a table
id|parentId|name|isMain
1|1|a1|null
2|1|a2|null
3|1|a3|null
4|2|b1|null
5|2|b2|1
6|2|b3| null
8|3|c1|1
9|3|c2|null
It is necessary to select records so that from each parentId group there is only one line GROP BY copes with this, but so that if the group has isMain 1, then it is displayed, if not, then any corresponding group.
In general, the output should be something from the series
1|1|a1|null
5|2|b2|1
8|3|c1|1

Answer 1

Option 1 - in the forehead. Formally works correctly (unlike the first two answers), but ugly:
SELECT * FROM test t1
WHERE
t1.is_main = (
SELECT MAX(is_main) FROM test t2 WHERE t2.parent_id = t1.parent_id
)
OR NOT EXISTS(SELECT * FROM test t2 WHERE t2.parent_id = t1.parent_id AND t2.is_main IS NOT NULL)
GROUP BY t1.parent_id


Answer 2

select id, parentid, name, max(ismain) as max_ismain from tablename group by parentid;

Answer 3

Wrote for fun trying to emulate T-SQL ROW_NUMBER on MySQL:

SELECT DISTINCT t.nid, f.* FROM (
  SELECT 
    id,
    parentId,
    isMain,
    @nid := CASE 
      WHEN @parentId <> parentId 
      THEN id
      ELSE @nid
    END AS nid,
    @parentId := parentId AS t2
  FROM hfeed 
  ORDER BY parentID, isMain DESC
) AS t
INNER JOIN hfeed AS f ON f.id = t.nid

Answer 4

Try ka
SELECT *, isMain IS NULL AS isnull FROM tablename ORDER BY isnull ASC GROUP BY parentId;

Answer 5

SELECT g.parentId, d.id, d.name, d.isMain
  FROM(SELECT DISTINCT parentId
          FROM my_table) g,
  JOIN my_table d
    ON d.id == (
       SELECT o.id
         FROM my_table o
        WHERE o.parentId == g.parentId
     ORDER BY o.isMain DESC)

You need an index on parentId, but you probably have it. And here it is == (SELECT ...)- this is probably a feature of SQLite, where such a construction returns a single value. In another dialect, you may need to write something like:

    ON d.id IN (
       SELECT o.id
         FROM my_table o
        WHERE o.parentId == g.parentId
     ORDER BY o.isMain DESC
        LIMIT 1)

Answer 6

Is this an option that might work?
Arrange indexes and perhaps optimize something else ...
Select id, ParentId, name, isMain
FROM dbo.Cash
where isMain is not null
UNION ALL
SELECT x.*
FROM dbo. x
inner join
(
Select ParentId, MIN(id) as id
FROM dbo.Cash
where parentId not in (Select ParentId FROM dbo.Cash where isMain is not null)
group by ParentId
) y
on x.id = y.id
order by 2