SQL Select Distinct with Conditional

Question

Table 1 has columns (id, a, b, c, group). There are several lines that have the same group, but the identifier is always unique. I would like the group SELECT, a, b FROM Table1 WHERE, the group is different. However, I would like the returned data to be from the row with the largest identifier for this group.

So if we have lines

(id=10, a=6, b=40, c=3, group=14)  
(id=5, a=21, b=45, c=31, group=230)  
(id=4, a=42, b=65, c=2, group=230)

I would like to return these 2 lines:

[group=14, a=6,b=40] and   
[group=230, a=21,b=45] (because id=5 > id=4)

Is there a simple SELECT statement?

Answer 1

You can do this using self-join or internal selection. Here's the internal selection:

select `group`, a, b from Table1 AS T1
   where id=(select max(id) from Table1 AS T2 where T1.`group` = T2.`group`)

And the method of self-connection:

select T1.`group`, T2.a, T2.b from
   (select max(id) as id,`group` from Table1 group by `group`) T1
   join Table1 as T2 on T1.id=T2.id

Answer 2

Try:

select grp, a, b
  from table1 where id in
    (select max(id) from table1 group by grp)

Answer 3

2 , :

SELECT MAX(id) FROM YourTable GROUP BY [GROUP]

.

, . .

SELECT [group], a, b FROM YourTable INNER JOIN
(SELECT MAX(id) FROM YourTable GROUP BY [GROUP]) t
ON t.id = YourTable.id

Answer 4

SELECT  mi.*
FROM    (
        SELECT  DISTINCT grouper
        FROM    mytable
        ) md
JOIN    mytable mi
ON      mi.id = 
        (
        SELECT  id
        FROM    mytable mo
        WHERE   mo.grouper = md.grouper
        ORDER BY
                id DESC
        LIMIT 1
        )

MyISAM id PRIMARY KEY, , (grouper, id).

InnoDB id PRIMARY KEY, grouper (id, PRIMARY KEY).

INDEX FOR GROUP-BY , id.

Answer 5

Don't know how to do this in mysql. But the following code will work for MsSQL ...

SELECT Y.* FROM
(
    SELECT DISTINCT [group], MAX(id) ID
    FROM Table1
    GROUP BY [group]
 ) X
INNER JOIN Table1 Y ON X.ID=Table1.ID