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

Your sample data does not appear to have any direct connection to the massive query below it. Can you explain what is happening here? — Tim Biegeleisen, Jul 10 '19 at 10:32
in my example are only 2 important columns OrdinaceId, Priloha2Id. I need to select all columns from select — Bobek, Jul 10 '19 at 10:36

score 1 · Answer 1 · answered Jul 10 '19 at 10:33

1

select Id, max(Priloha2Id) from table group by Id

answered Jul 10 '19 at 10:33

megha shah

21
6

I have modified your answer for my needs select OrdinaceId, CleneniPzsId, Id, Ico, Icz, Zkratka, Icp, OdbornostKod, OrdinaceNazev, OrdinaceAdresaCela, Max(Priloha2Id) , Smluvni, EP2, StavPzp, Status, Info, PlatnostOd, PlatnostDo from (select........) a group by OrdinaceId and getting following error Column 'a.CleneniPzsId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Bobek Jul 10 '19 at 10:47

Renat · Accepted Answer · 2019-07-10T11:09:47.627

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 ;

@Bobek, I've fixed my query, was joining to wrong table at first — Renat, Jul 10 '19 at 11:10

SQL select unique column with maximum for 2nd column

2 Answers2