-2

I have loaded multiple files in SQL Server.

Let's Say

table structure

Now

select COL_A, min(COL_C),max(COL_C) 
from tbl
group by COL_A

I want to write SQL query to find which file has that Min(COL_C) and Max(COL_C) value. As you can see I am storing FILENAME in table.

So I want result like this

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SaurabhDeshpande
  • 19
  • 5
  • So what is your question? What's stopping you achieving what you want? Also hyperlinks to images of text aren't helpful. Post text as text. There are multiple ways to format data in a tabular format in your question. – Thom A May 02 '21 at 16:59
  • How would you handle a tie where each file had the same maximum or minimum? – Stu May 02 '21 at 17:14
  • For Lamu : I want to find out my MIN(COL_C) and MAX(COL_C) by COL_A is coming from which file. – SaurabhDeshpande May 02 '21 at 17:21
  • For Stu : That fine for me if MIN and MAX value are from multiple files. – SaurabhDeshpande May 02 '21 at 17:22
  • I'm sure it's fine - but looking at your image of how you want the results, presumably you'd expect 2 rows for the same col_A value. – Stu May 02 '21 at 17:27
  • Stu : that exactly is my problem statement, I want to display in one row. – SaurabhDeshpande May 03 '21 at 05:38

1 Answers1

0

Here's one possible way you could get your results, using apply to correlate each aggregated value back to the same table to find the filename for that value, and using string_agg to produce a delimeted list if there are ties.

select * from (
    select COL_A, Min(COL_C) MinColC,Max(COL_C) MaxColC
    from T
    group by COL_A
)x
outer apply (
    select string_agg([filename], ', ') sMinFilename
    from T
    where T.COL_A=x.COL_A and T.COL_C=x.MinColC
)mn
outer apply (
    select string_agg([filename], ', ') MinFilename
    from T
    where T.COL_A=x.COL_A and T.COL_C=x.MaxColC
)mx
Stu
  • 30,392
  • 6
  • 14
  • 33