SQL Question
SQL | Return MIN values | multiple rows dynamically
I need to create a query that returns only the rows which have the minimum values in a specific column.
I have this results (example):
Name | Description | Order
-----------------------------
Name1 | A | 1
Name1 | B | 2
Name1 | C | 3
Name2 | A | 1
Name2 | B | 2
Name2 | C | 3
I want to get this results:
Name | Description | Order
-----------------------------
Name1 | A | 1
Name2 | A | 1
Basically, I need to select only the rows which have the minimum value in the column order.
I'm stuck with queries like Query A:
Select Top 1 *
From table1
Where Name = 'Name1'
Order by Order
Or Query B:
Select *
From table1
Where Name = 'Name1'
and order = (Select min(Order)
From table1
Where Name = 'Name1')
What I need is to have in a single query, the capability of returning the rows for multiple names, instead of having to do queries per name. Is there any way, to pass attributes values to the subquery in the example of Query B?
What are the alternatives?
The platform is SQL Server 2012.
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
You're close on the second query if you want to use a simple subquery:
Select *
From table1 t1
WHERE [order] = (Select min([Order])
From table1
Where Name = t1.Name)
You can also use partitioning functions to find the "first" record for each group:
SELECT Name, Description, [Order] FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY [Order] PARTITION BY Name) RowNum
FROM Table1
) A
WHERE RowNum = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download