SQL MIN, MAX window query with references

Question

I have a working query from the report using min and max window function, but I need to pull the references of min and max. Here's the main query:

SELECT 
    company, 
    countryname,
    dept,
    unit,
    score,
    MAX(score) OVER(PARTITION BY company) AS max_comp_score,
    --need reference countryname, dept, unit for this max score

    MIN(score) OVER(PARTITION BY company) AS min_comp_score,
    --need reference countryname, dept, unit for this min score

    AVG(score) OVER(PARTITION BY company) AS avg_comp_score

FROM 

tt.vw_Score_Report

The view is quite big (5 mil rows), so if I do sub-queries on min and max to pull their references from there, it takes ages to retrieve them.

What are the options to get those attributes to the query? Thought there could be some elegant way of solving that.

What do you mean the "attribute" of the `MIN` and `MAX`? `MIN` and `MAX` return a scalar value, they don't have other "attributes". — Thom A, Sep 16 '20 at 13:51
It's in the query comments. I want to have the country, dept and unit which has this min score. Then company, dept, unit which has that max score for references. — RandyMcKay, Sep 16 '20 at 13:52
`The view is quite big (5 mil rows)` that's not big, that's small data. SQL Server can handle multi-TB databases with billions of records. Views have no data anyway, tables do. If you have performance issues you need to investigate them. Perhaps the view is too complex, perhaps it perfoms operations that prevent the use of indexes? Perhpas the tables don't have the appropriate indexes? — Panagiotis Kanavos, Sep 16 '20 at 13:53
What you are after isn't the "min" and "max" then. It's what's demonstrated in questions like [How to get first record out of each group from the result retrieved by using group by command](https://stackoverflow.com/q/5225113/2029983) and [Get top 1 row of each group](https://stackoverflow.com/q/6841605/2029983) — Thom A, Sep 16 '20 at 13:53
You mean, you want the record with the minimum or maximum score? Please post the definition of `vw_Score_Report`. There's no reason you can't use `HAVING MIN()` or `HAVING MAX()`. It's not the most elegant way but it shouldn't take ages. Maybe a couple of seconds — Panagiotis Kanavos, Sep 16 '20 at 13:56
@Larnu, not sure I follow. It is about min and max and the units having those min or max values. — RandyMcKay, Sep 16 '20 at 13:57
Which is *exactly* what those answers are showing you, @RandyMcKay ... — Thom A, Sep 16 '20 at 13:58

score 2 · Accepted Answer · answered Sep 16 '20 at 14:04

You can use a CROSS APPLY to get the min and max rows:

SELECT 
    company, 
    countryname,
    dept,
    unit,
    score,
    srmin.countryname,
    srmin.dept,
     ...
    srmax.countryname,
    srmax.dept,
    srmax.score,
    .....
    AVG(score) OVER(PARTITION BY company) AS avg_comp_score

FROM tt.vw_Score_Report sr
CROSS APPLY (   SELECT TOP 1 countryname, dept, unit ,score
                FROM tt.vw_Score_Report sr2
                WHERE sr2.company = sr.company
                ORDER BY score DESC
    ) srmax
CROSS APPLY (   SELECT TOP 1 countryname, dept, unit ,score
                FROM tt.vw_Score_Report sr3
                WHERE sr3.company = sr.company
                ORDER BY score ASC
    ) srmin

Thank you Wouter, that worked. Need to get into CROSS APPLY really. — RandyMcKay, Sep 16 '20 at 14:21
Nice - I don't do much with `Cross Apply`, either. I'd be interested in a performance comparison between these two solutions, if you're of a mind to run both and report the results. — Brian, Sep 16 '20 at 15:30

score 1 · Answer 2 · answered Sep 16 '20 at 14:09

I'd try something like this (you'll want to alias the columns - this is just a sketch)

;With cte_Co_Max
As
(
    Select 
        company, 
        dept,
        unit,
        score,
        Row_Number() Over (Partition By company Order By score Desc) As rn
    From tt.vw_Score_Report
), cte_Co_Min
As
(
    Select 
        company, 
        dept,
        unit,
        score,
        Row_Number() Over (Partition By company Order By score) As rn
    From tt.vw_Score_Report
)

Select 
    vsr.company, 
    vsr.countryname,
    vsr.dept,
    vsr.unit,
    vsr.score,

    cc_Max.company, 
    cc_Max.countryname,
    cc_Max.dept,
    cc_Max.unit,
    cc_Max.score,

    cc_Min.company, 
    cc_Min.countryname,
    cc_Min.dept,
    cc_Min.unit,
    cc_Min.score,

    AVG(score) OVER(PARTITION BY company) AS avg_comp_score

FROM 

tt.vw_Score_Report As vsr
    Inner Join cte_Co_Max As cc_Max
        On cc_Max.company = vsr.company
        and cc_Max.rn = 1
    Inner Join cte_Co_Min As cc_Min
        On cc_Min.company = vsr.company
        and cc_Min.rn = 1

Since I didn't have sample data, I couldn't test it, of course.

Thank you Brian, I had similar query which had to work but was not returning results for ages. Upvoted. — RandyMcKay, Sep 16 '20 at 14:20

SQL MIN, MAX window query with references

2 Answers2