SQL select unique column with maximum for 2nd column

Question

Please how to combine maximum and distinct to have always unique Id and maximum of the column for that unique Id?

For example, if I have,

OrdinaceId Priloha2Id
5            1
5            2
6            2
6            4
7            1

result will be

OrdinaceId Priloha2Id
5             2
6             4
7             1

how to return only 1 row(with the same Id) with a maximum of Priloha2Id?

select * from (select 
distinct ord.Id as OrdinaceId,
ord.CleneniPzsId,
  posk.Id,
  posk.ICO as Ico,
  zar.ICZ as Icz,
  posk.NazevZkraceny as Zkratka,
  case when cle.Primariat = 0 then cle.Icp end as Icp,
  IIF(cle.Primariat = 1, pri.OborKod , cle.OdbornostKod) as OdbornostKod,
  ord.Nazev as OrdinaceNazev,
  (select Street + N' ' + DescriptiveNo + N', ' + PostCode +  N' ' + City from ICIS_Repl.repl.Address where Code = ord.NavAddressCode and Type = N'ORDINACE' and (ValidFrom is null or ValidFrom <= GETDATE()) and (ValidTill is null or ValidTill >= GETDATE() or ValidTill = N'1753-01-01 00:00:00')) as OrdinaceAdresaCela,
  pril.Id as Priloha2Id,
  cle.Smluvni,
  cisP2KomunikaceStav.Nazev as EP2,
  cisPzs.Nazev as StavPzp,
  pril.Status,
  pril.Info,
  pril.PriPlatnostOd as PlatnostOd,
  pril.PriPlatnostDo as PlatnostDo
from Ordinace ord
  left join CleneniPzs cle on cle.Id = ord.CleneniPzsId
  left join ZarizeniPZS zar on cle.ZarizeniPzsId = zar.Id
  left join PoskytovatelZS posk on zar.PoskytovatelZsId = posk.Id
  left join Primariat pri on cle.PrimariatId = pri.Id
  left join Pril2Formular pr on pr.CleneniPzsId = cle.Id
  left join Priloha2 pril on pril.Id = pr.Priloha2Id and (pril.PriPlatnostOd <= GETDATE() or pril.PriPlatnostOd is null) and (pril.PriPlatnostDo is null or pril.PriPlatnostDo >= GETDATE()) 
  join CisCiselnik cisP2KomunikaceStav on posk.P2KomunikaceStavKod = cisP2KomunikaceStav.Kod AND cisP2KomunikaceStav.Oblast = N'P2KomunikaceStav' and cisP2KomunikaceStav.PlatnostOd <= GETDATE() and (cisP2KomunikaceStav.PlatnostDo is null or cisP2KomunikaceStav.PlatnostDo >= GETDATE()) 
  left join P2Formular form on form.Icp = cle.Icp and form.Aktivni = 1
  left join CisCiselnik cisPzs on form.P2StavPzpKod = cisPzs.Kod AND cisPzs.Oblast = N'P2StavPZP' and cisPzs.PlatnostOd <= GETDATE() and (cisPzs.PlatnostDo is null or cisPzs.PlatnostDo >= GETDATE())  
  left join P2ZarizeniPZS z on form.P2ZarizeniPzsId = z.Id and z.Icz = zar.ICZ and z.PoskytovatelZsId = posk.Id
  join Smlouva smlv on smlv.Id = pril.SmlouvaId
  left join SmluvniVykon smlVyk on smlVyk.CleneniPzsId = cle.Id and smlVyk.PlatnostOd <= GETDATE() and (smlVyk.PlatnostDo is null or smlVyk.PlatnostDo >= GETDATE())
  left join SmlVykonVyjadreniRL vyjadreni on vyjadreni.Id = smlVyk.VyjadreniRLId ) a

Renat · Accepted Answer

You may use CTE to get max(Priloha2Id) per id. Then join to it.

Something like:

;with Pril2Formular_max_per_id as (
    select ord.Id as OrdinaceId, max(pr.Priloha2Id) as max_Priloha2Id
    from Ordinace ord
      left join CleneniPzs cle on cle.Id = ord.CleneniPzsId
      left join Pril2Formular pr on pr.CleneniPzsId = cle.Id
    group by ord.Id
)
select * from (select 
distinct ord.Id as OrdinaceId,[![enter image description here][1]][1]
ord.CleneniPzsId,
  posk.Id,
  posk.ICO as Ico,
  zar.ICZ as Icz,
  posk.NazevZkraceny as Zkratka,
  case when cle.Primariat = 0 then cle.Icp end as Icp,
  IIF(cle.Primariat = 1, pri.OborKod , cle.OdbornostKod) as OdbornostKod,
  ord.Nazev as OrdinaceNazev,
  (select Street + N' ' + DescriptiveNo + N', ' + PostCode +  N' ' + City from ICIS_Repl.repl.Address where Code = ord.NavAddressCode and Type = N'ORDINACE' and (ValidFrom is null or ValidFrom <= GETDATE()) and (ValidTill is null or ValidTill >= GETDATE() or ValidTill = N'1753-01-01 00:00:00')) as OrdinaceAdresaCela,
  pril.Id as Priloha2Id,
  cle.Smluvni,
  cisP2KomunikaceStav.Nazev as EP2,
  cisPzs.Nazev as StavPzp,
  pril.Status,
  pril.Info,
  pril.PriPlatnostOd as PlatnostOd,
  pril.PriPlatnostDo as PlatnostDo
from Ordinace ord
  left join CleneniPzs cle on cle.Id = ord.CleneniPzsId
  left join ZarizeniPZS zar on cle.ZarizeniPzsId = zar.Id
  left join PoskytovatelZS posk on zar.PoskytovatelZsId = posk.Id
  left join Primariat pri on cle.PrimariatId = pri.Id
  left join Pril2Formular_max_per_id pr on pr.OrdinaceId = ord.Id
  left join Priloha2 pril on pril.Id = pr.max_Priloha2Id and (pril.PriPlatnostOd <= GETDATE() or pril.PriPlatnostOd is null) and (pril.PriPlatnostDo is null or pril.PriPlatnostDo >= GETDATE()) 
  join CisCiselnik cisP2KomunikaceStav on posk.P2KomunikaceStavKod = cisP2KomunikaceStav.Kod AND cisP2KomunikaceStav.Oblast = N'P2KomunikaceStav' and cisP2KomunikaceStav.PlatnostOd <= GETDATE() and (cisP2KomunikaceStav.PlatnostDo is null or cisP2KomunikaceStav.PlatnostDo >= GETDATE()) 
  left join P2Formular form on form.Icp = cle.Icp and form.Aktivni = 1
  left join CisCiselnik cisPzs on form.P2StavPzpKod = cisPzs.Kod AND cisPzs.Oblast = N'P2StavPZP' and cisPzs.PlatnostOd <= GETDATE() and (cisPzs.PlatnostDo is null or cisPzs.PlatnostDo >= GETDATE())  
  left join P2ZarizeniPZS z on form.P2ZarizeniPzsId = z.Id and z.Icz = zar.ICZ and z.PoskytovatelZsId = posk.Id
  join Smlouva smlv on smlv.Id = pril.SmlouvaId
  left join SmluvniVykon smlVyk on smlVyk.CleneniPzsId = cle.Id and smlVyk.PlatnostOd <= GETDATE() and (smlVyk.PlatnostDo is null or smlVyk.PlatnostDo >= GETDATE())
  left join SmlVykonVyjadreniRL vyjadreni on vyjadreni.Id = smlVyk.VyjadreniRLId ) a ;

SQL select unique column with maximum for 2nd column

Answers (2)

Related Questions