CODEWITHSUNDEEP
mysqlsqlselectdistinct
Facutz
Facutz

Reputation: 437

SELECT DISTINCT on one column considering another column

I have

    Id          Number
----------- -----------
950         20213666062
951         20213666062

I want only one time each "number", the one with the highest Id:

    Id          Number
----------- -----------
951         20213666062

SELECT
rarN.intIdRARNomina AS Id,
rarN.chrCUIL AS Number           
FROM dbo.PVN_RAR p
        INNER JOIN dbo.PVN_RARNomina rarN ON p.intIdRAR = rarN.intIdRAR
        INNER JOIN PISCYS.dbo.SYA_UltimosContratoCliente uc ON p.intNroContrato = uc.intNroContrato
WHERE
        p.intIdRAR = 4639

Upvotes: 0

Views: 36

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522626

Try using a simple GROUP BY query:

SELECT MAX(Id) AS Id, Number
FROM yourTable
GROUP BY Number;

In the context of your updated question/query:

SELECT
    MAX(rarN.intIdRARNomina) AS Id,
    rarN.chrCUIL AS Number
FROM dbo.PVN_RAR p
INNER JOIN dbo.PVN_RARNomina rarN
    ON p.intIdRAR = rarN.intIdRAR
INNER JOIN PISCYS.dbo.SYA_UltimosContratoCliente uc
    ON p.intNroContrato = uc.intNroContrato
WHERE
    p.intIdRAR = 4639
GROUP BY
    Number;

Upvotes: 1

Eray Balkanli
Eray Balkanli

Reputation: 7990

Another alternative is to use a self join:

select t.*
from table t
inner join (select max(id) as DID, number from table group by number) t2
on t.ID = t2.DID

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You need correlated subquery if you have a more columns than this else only group by with max() is enough to achieve the desire result :

select t.*
from table t
where t.id = (select max(t1.id) from table t1 where t1.Number = t.Number);

However, Latest version has one more option to achieve this by using row_number().

Upvotes: 0

Related Questions