Using group by in a sql query for remuving duplicates

Majid Hojati Published at Dev

Majid Hojati

I have a query like this:

select 
o.Name,
o.Family,
o.phone,
o.OBJECTID,
o.owner_id,
l.Licence_Contex,
l.Licence_title,
l.Id 
from 
[dbo].[owner_licence] as l,[dbo].[OWNER] as o 
where o.[owner_id] =l.[Owner_ID] 
    And l.Id 
    NOT IN 
        (select l.id from [dbo].[owner_licence] as l,[dbo].[OWNER] as o 
        where o.[owner_id] =l.[Owner_ID] 
        And (l.Registration_date1  > DATEADD(year, -1, GetDate()) 
        or l.[Registration_date2]> DATEADD(year, -1, GetDate()) 
        or l.[Registration_date3]> DATEADD(year, -1, GetDate()) 
        or l.[Registration_date4] > DATEADD(year, -1, GetDate()) 
        or l.[Registration_date5]> DATEADD(year, -1, GetDate())))

the result is some how like this

john    smith   09305689220 1080199884  1   licencetitle_1  licencecontex_1 10
John    Smith   09305689220 1080199884  1   licencetitle_2 licencecontex3   13

As you can see the both of these rows are for the same person and I want to aggregate these duplicate persons into one row..Is there any way to do it?I useg group by o.owner_id and also distinct but they does not work..

FuzzyTree

The subquery restricts the license to the one with the most maximum (qualified) id for each owner, so you should only get 1 license (and therefore 1 row) per owner.

select 
o.Name,
o.Family,
o.phone,
o.OBJECTID,
o.owner_id,
l.Licence_Contex,
l.Licence_title,
l.Id 
from 
[dbo].[owner_licence] as l
join [dbo].[OWNER] as o on o.[owner_id] = l.[Owner_ID]
join (select 
    max(ol.id) max_lid,
    ol.owner_id
from
    owner_licence ol
where ol.id not in (select l.id from [dbo].[owner_licence] as l,[dbo].[OWNER] as o 
        where o.[owner_id] =l.[Owner_ID] 
        And (l.Registration_date1  > DATEADD(year, -1, GetDate()) 
        or l.[Registration_date2]> DATEADD(year, -1, GetDate()) 
        or l.[Registration_date3]> DATEADD(year, -1, GetDate()) 
        or l.[Registration_date4] > DATEADD(year, -1, GetDate()) 
        or l.[Registration_date5]> DATEADD(year, -1, GetDate())))
group by
    ol.owner_id) t1 on t1.owner_id = o.owner_id AND t1.max_lid = l.id

Collected from the Internet

Please contact [email protected] to delete if infringement.