SQL select unique列，第2列具有最大值

Question

请问如何组合maximum和distinct以始终具有唯一Id和该唯一Id的列的最大值？

例如，如果我有，

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

结果将是

OrdinaceId Priloha2Id
5             2
6             4
7             1

如何仅返回一行(具有相同Id)且最大值为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 

​

​

Answer 1

您可以使用CTE来获取每个id的最大值(Priloha2Id)。那就加入它吧。

类似于：

;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 ;

Answer 2

从表group by Id中选择Id，max(Priloha2Id)