SQL Select Distinct with Conditional

Question

Table1 has columns (id, a, b, c, group). There are several rows that have the same group, but id is always unique. I would like to SELECT group,a,b FROM Table1 WHERE the group is distinct. However, I would like the returned data to be from the row with the greatest id for that group.

Thus, if we have the rows

(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 rows:

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

Is there a simple SELECT statement to do this?

Answer 1

You can do it using a self join or an inner-select. Here's inner select:

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

And self-join method:

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 selects, your inner select gets:

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

Your outer select joins to this table.

Think about it logically, the inner select gets a sub set of the data you need. The outer select inner joins to this subset and can get further data.

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
        )

If your table is MyISAM or id is not a PRIMARY KEY, then make sure you have a composite index on (grouper, id).

If your table is InnoDB and id is a PRIMARY KEY, then a simple index on grouper will suffice (id, being a PRIMARY KEY, will be implictly included).

This will use an INDEX FOR GROUP-BY to build the list of distinct groupers, and for each grouper it will use the index access to find the maximal id.

Answer 5

Don't know how to do it 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

Answer 6

Hi

Thank you very helpfull solution for me to :-)

in my case Table1 is nested select. No proble in that. But I have to insert the nested select twice (replacing table1 in above example), which makes the qery very big and "repeating" - cant I somehow set a alias for the nested select? (tried different ways - none works)

Kind Regards Nicolai