SQL Server minimum value within column

Question

I have table_1 with the following data:

| STORE | Add | dis | Cnt | 
+-------+-----+-----+-----+
|   101 | X   | abc | 2   | 
|   101 | X   | null| 3   | 
|   101 | X   |pqrd | 4   | 
|   101 | X   | null| 1   | 
|   102 | y   | null| 1   | 
|   102 | y   | xyz | 3   | 
|   102 | y   | pqr | 4   | 
|   102 | y   | null| 2   |

I tried to build a query to get data from table_1 where [dis] is not null and [cnt] should be minumum. So my result should looks like below:

| STORE | Add | dis | Cnt | 
+-------+-----+-----+-----+
|   101 | X   | abc | 2   | 
|   102 | y   | xyz | 3   |

My query looks like below :

SELECT store,add,dis,min(TMPLT_PRIORITY_NMBR)
FROM table_1 group by store,add;

But I get the following error:

Column 'dis' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I use [dis] in GROUP BY clause, I get the wrong result and giving max(dis) or min(dis) also provides the wrong result.

What would be the solution for this issue?

Mureinik · Accepted Answer · 2019-05-08 22:25:44Z

3

You could use rank to find the row with the minimal cnt value per store/add combination, and return all the columns from it:

SELECT store, add, dis, cnt
FROM   (SELECT *, RANK() OVER (PARTITION BY store, add ORDER BY cnt) AS rk
        FROM   table_1
        WHERE  dis IS NOT NULL) t
WHERE  rk = 1

answered May 8, 2019 at 22:25

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zohar Peled · Accepted Answer · 2019-05-08 22:58:14Z

2

Another option would be to use first_value and min with over:

SELECT distinct store,
       add,
       first_value(dis) over(partition by store, add order by Cnt) as dis,
       min(Cnt) over(partition by store, add) as cnt
FROM table_1

answered May 8, 2019 at 22:58

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Collectives™ on Stack Overflow

SQL Server minimum value within column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related