CODEWITHSUNDEEP
sqlmysql
nedblorf
nedblorf

Reputation: 5365

SQL Select Distinct with Conditional

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?

Upvotes: 4

Views: 6903

Answers (5)

Faiz
Faiz

Reputation: 5453

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

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838326

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

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425411

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.

Upvotes: 1

JonH
JonH

Reputation: 33153

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

Upvotes: 1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181290

Try:

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

Upvotes: 2

Related Questions