Group rows by same values and return only one?

Question

I want my query to return only ONE row (first row) for each unique LetoZrChr (so 5 rows total).

Here's what it currently does:

It returns all rows (258) without grouping the information by the column I want it to.

My current SQL:

SELECT a.maticna, a.clientNo,  a.letoZr, a.letoZrChr, a.sort, a.tipOcene, a.bonitetniModel, a.datumOtvoritve,
    im.PKModul1_LT as ocm1solor_LT, im.PDModul1_LT*100 as ocm1solo_LT, im.PKModul1_ST as ocm1solor_ST, im.PDModul1_ST*100 as ocm1solo_ST, im.DatumKLJ    
FROM #osnovna a 
LEFT JOIN airb.bm.IzhodModeli im on a.clientNo=im.KomitentID
    AND im.ReferencniDatum=a.datumOtvoritve
    AND a.tipocene = im.tipocene
    --GROUP BY letoZrChr    
ORDER BY datumOtvoritve DESC, im.DatumKLJ DESC

The return of this SQL (I've purposely left some rows out, as it returns 258 rows, but you'll get the idea from the rows below):

maticna clientNo letoZr letoZrChr   sort    tipOcene    bonitetniModel  datumOtvoritve  ocm1solor_LT    ocm1solo_LT ocm1solor_ST    ocm1solo_ST DatumKLJ
5046432 975145  2019    PL2019  5   3   GD  2019-12-31  NULL    NULL    NULL    NULL    NULL
5046432 975145  2019    ML2019  4   2   GD  2019-06-30  4   0,868   3   0,565   43899
5046432 975145  2019    ML2019  4   2   GD  2019-06-30  4   0,868   3   0,565   43898
5046432 975145  2019    ML2019  4   2   GD  2019-06-30  4   0,868   3   0,565   43897
5046432 975145  2019    ML2019  4   2   GD  2019-06-30  4   0,868   3   0,565   43896
5046432 975145  2018    ZR2018  3   1   GD  2018-12-31  7   8,098   6   5,236   43899
5046432 975145  2018    ZR2018  3   1   GD  2018-12-31  7   8,098   6   5,236   43898
5046432 975145  2018    ZR2018  3   1   GD  2018-12-31  7   8,098   6   5,236   43897
5046432 975145  2018    ZR2018  3   1   GD  2018-12-31  7   8,098   6   5,236   43896
5046432 975145  2018    ZR2018  3   1   GD  2018-12-31  7   8,098   6   5,236   43895
5046432 975145  2018    ZR2018  3   1   GD  2018-12-31  7   8,098   6   5,236   43894
5046432 975145  2018    ZR2018  3   1   GD  2018-12-31  7   8,098   6   5,236   43893
5046432 975145  2018    ZR2018  3   1   GD  2018-12-31  7   8,098   6   5,236   43892
5046432 975145  2018    ZR2018  3   1   GD  2018-12-31  7   8,098   6   5,236   43891
5046432 975145  2018    ZR2018  3   1   GD  2018-12-31  7   8,098   6   5,236   43890
5046432 975145  2018    ZR2018  3   1   GD  2018-12-31  6   5,714   5   2,601   43611
5046432 975145  2017    ZR2017  2   1   GD  2017-12-31  6   4,652   5   2,105   43607
5046432 975145  2017    ZR2017  2   1   GD  2017-12-31  6   4,652   5   2,105   43594
5046432 975145  2017    ZR2017  2   1   GD  2017-12-31  6   4,652   5   2,105   43591
5046432 975145  2017    ZR2017  2   1   GD  2017-12-31  6   4,66    5   2,095   43584
5046432 975145  2017    ZR2017  2   1   GD  2017-12-31  6   4,66    5   2,095   43562
5046432 975145  2017    ZR2017  2   1   GD  2017-12-31  6   4,66    5   2,095   43561
5046432 975145  2017    ZR2017  2   1   GD  2017-12-31  6   4,66    5   2,095   43559
5046432 975145  2017    ZR2017  2   1   GD  2017-12-31  6   4,66    5   2,095   43558
5046432 975145  2017    ZR2017  2   1   GD  2017-12-31  6   4,711   5   2,034   43410
5046432 975145  2017    ZR2017  2   1   GD  2017-12-31  6   4,711   5   2,034   43403
5046432 975145  2016    ZR2016  1   1   GD  2016-12-31  5   2,86    NULL    NULL    43244
5046432 975145  2016    ZR2016  1   1   GD  2016-12-31  5   2,86    NULL    NULL    43243
5046432 975145  2016    ZR2016  1   1   GD  2016-12-31  5   2,86    NULL    NULL    43242
5046432 975145  2016    ZR2016  1   1   GD  2016-12-31  5   2,86    NULL    NULL    43241
5046432 975145  2016    ZR2016  1   1   GD  2016-12-31  5   2,86    NULL    NULL    43206
5046432 975145  2016    ZR2016  1   1   GD  2016-12-31  5   2,86    NULL    NULL    43202
5046432 975145  2016    ZR2016  1   1   GD  2016-12-31  5   2,86    NULL    NULL    43201

So basically what I want to do is, that the query only returns the first row of each "letoZrChr", which is are for example "ZR2016", or "ML2019", etc.

Answer 1

If I understand the question correctly, you need to use ROW_NUMBER():

SELECT *
FROM (
   SELECT 
      a.maticna, a.clientNo,  a.letoZr, a.letoZrChr, a.sort, a.tipOcene, a.bonitetniModel, a.datumOtvoritve,
      im.PKModul1_LT as ocm1solor_LT, im.PDModul1_LT*100 as ocm1solo_LT, im.PKModul1_ST as ocm1solor_ST, im.PDModul1_ST*100 as ocm1solo_ST, im.DatumKLJ,
      ROW_NUMBER() OVER (PARTITION BY a.letoZrChr ORDER BY datumOtvoritve DESC, im.DatumKLJ DESC) AS rn
   FROM #osnovna a 
   LEFT JOIN 
      airb.bm.IzhodModeli im on a.clientNo=im.KomitentID AND 
      im.ReferencniDatum=a.datumOtvoritve AND 
      a.tipocene = im.tipocene
) t
WHERE t.rn = 1