Grouping results by a column without using group by clause

Question

I have the below query:

SELECT 
   s.nsn,
   CAST(ms.msDli AS VARCHAR) + CAST(pe.peDli AS VARCHAR) as dli,
   p.engineNo, m.mopNo, sh.carName, 
   s.qty, s.msdSr, s.msdUr,
   (SELECT MAX(s.Qty) FROM spares s) MaxQuantity
FROM 
   spares s, mop m, pms p, car sh, subEquipment sub, mainSystem ms, parentEquipment pe, car_parentEquipment spe
WHERE 
    s.mopId = m.mopId 
    AND m.pmsId = p.pmsId 
    AND p.subId = sub.subId 
    AND sub.peId = pe.peId 
    AND sub.shId = sh.shId 
    AND pe.peId = spe.peId 
    AND spe.shId = sh.shId 
    AND pe.msId = ms.msId
ORDER BY 
    s.demandDate DESC, s.nsn

Which returns result in a following manner:

nsn dli engineNo    mopNo   carName qty msdSr   msdUr   MaxQuantity
098 58011   235     235     TOYOTA  50  98      98          897
235 58011   235     235     TOYOTA  12  25      235     897
098 55511   98      987     COROLLA 98  98      908         897
897 58011   235     235     TOYOTA  23  897     897     897

What I want is to club this result according to first column i.e. NSN

Such the all cars with a similar NSN shows together and the Last column shows the Max quantity (is the max from qty. column from them:

For e.g. in above example the result should look like this:

nsn dli engineNo    mopNo   carName qty msdSr   msdUr   MaxQuantity
098 58011   235     235    TOYOTA   98  98      98      98
098 55511   98      987     COROLLA 50  98      908     98
235 58011   235     235     TOYOTA  235 25      235     12
897 58011   235     235     TOYOTA  897 897     897     23

I don’t want a Group By solution as it will ask me to include all columns in group by or aggregate clause and cant understand that mess, please help me out with a solution with sub queries or joins if possible?

---

Applying Gordon Linoff solution

with t as (
    SELECT 
       s.nsn,
       CAST(ms.msDli AS VARCHAR) + CAST(pe.peDli AS VARCHAR) as dli,
       p.engineNo, m.mopNo, sh.carName, 
       s.qty, s.msdSr, s.msdUr,
       (SELECT MAX(s.Qty) FROM spares s) MaxQuantity
    FROM 
       spares s, mop m, pms p, car sh, subEquipment sub, mainSystem ms, parentEquipment pe, car_parentEquipment spe
    WHERE 
        s.mopId = m.mopId 
        AND m.pmsId = p.pmsId 
        AND p.subId = sub.subId 
        AND sub.peId = pe.peId 
        AND sub.shId = sh.shId 
        AND pe.peId = spe.peId 
        AND spe.shId = sh.shId 
        AND pe.msId = ms.msId
    ORDER BY 
        s.demandDate DESC, s.nsn)
    select t.*, max(qty) over (partition by nsn) as MaxQuantity
    from t
    order by nsn;

Answer 1

It sounds like you want the window function max() over. This will get the maximum value for each group, but, instead of grouping the values, it just appends the value to each row:

with t as (your query here)
    select t.*, max(qty) over (partition by nsn) as MaxQuantity
    from t
    order by nsn;