CODEWITHSUNDEEP
sqlt-sql
TamarindoSean
TamarindoSean

Reputation: 91

T-SQL : Return variable amount of records based on the top value in a column

If I have table:

+------------------------------------------------------------+
|  Col 1                                     Col 2    Col 3  |
+------------------------------------------------------------+
|  a72195fa-57ca-4849-b423-5595219c8f6b      Val1     1      |
|  a72195fa-57ca-4849-b423-5595219c8f6b      Val2     1      |
|  a72195fa-57ca-4849-b423-5595219c8f6b      Val3     2      |
|  a72195fa-57ca-4849-b423-5595219c8f6b      Val4     3      |
|  2F6D0B9E-2221-4D18-A64E-ACCFB83EACC7      Val5     2      |
|  2F6D0B9E-2221-4D18-A64E-ACCFB83EACC7      Val6     2      |
|  2F6D0B9E-2221-4D18-A64E-ACCFB83EACC7      Val7     3      |
+------------------------------------------------------------+

For a given Col 1 Value, how do I return all records for smallest value in Col3. Meaning where [Col 1] = a72195fa-57ca-4849-b423-5595219c8f6b, I want to return:

+------------------------------------------------------------+
|  Col 1                                     Col 2    Col 3  |
+------------------------------------------------------------+
|  a72195fa-57ca-4849-b423-5595219c8f6b      Val1     1      |
|  a72195fa-57ca-4849-b423-5595219c8f6b      Val2     1      |
+------------------------------------------------------------+

And where [Col 1] = 2F6D0B9E-2221-4D18-A64E-ACCFB83EACC7, I want to return:

+---------------------------------------------------------+
| Col 1                                   Col 2    Col 3  |
+---------------------------------------------------------+
| 2F6D0B9E-2221-4D18-A64E-ACCFB83EACC7    Val5     2      |
| 2F6D0B9E-2221-4D18-A64E-ACCFB83EACC7    Val6     2      |
+---------------------------------------------------------+

Upvotes: 0

Views: 148

Answers (4)

Steve Kass
Steve Kass

Reputation: 7184

Here is basically the same query Lee gave, expressed a bit more compactly.

select top (1) with ties
  Col1, Col2, Col3
from T
order by rank() over (
  partition by Col1
  order by Col3
)

Upvotes: 0

Lee Tickett
Lee Tickett

Reputation: 6027

A bit smarter (i think):

SELECT * INTO #TEMP
FROM
(
 SELECT 'a72195fa-57ca-4849-b423-5595219c8f6b' col1, 'Val1' col2, 1 col3
UNION SELECT 'a72195fa-57ca-4849-b423-5595219c8f6b' col1, 'Val2' col2, 1 col3
UNION SELECT 'a72195fa-57ca-4849-b423-5595219c8f6b' col1, 'Val3' col2, 2 col3
UNION SELECT 'a72195fa-57ca-4849-b423-5595219c8f6b' col1, 'Val4' col2, 3 col3
UNION SELECT '2F6D0B9E-2221-4D18-A64E-ACCFB83EACC7' col1, 'Val5' col2, 2 col3
UNION SELECT '2F6D0B9E-2221-4D18-A64E-ACCFB83EACC7' col1, 'Val6' col2, 2 col3
UNION SELECT '2F6D0B9E-2221-4D18-A64E-ACCFB83EACC7' col1, 'Val7' col2, 3 col3
) A

SELECT * FROM #TEMP

SELECT *
FROM
(
 SELECT *
 , RANK() OVER(PARTITION BY col1 ORDER BY col3 ASC) _RANK
 FROM #TEMP
) a
WHERE a._RANK = 1

Upvotes: 2

Oded
Oded

Reputation: 498942

Here is one way using a subquery:

SELECT *
FROM myTable
WHERE Col3 = (SELECT MIN(Col3) FROM myTable WHERE Col1 = @SomePassedInGuid)
AND Col1 = @SomePassedInGuid

Upvotes: 1

Teja
Teja

Reputation: 13524

SELECT A.*
FROM TABLE A,
(
SELECT Col1,MIN(Col3) AS Col3
FROM TABLE
GROUP BY Col1
) B
WHERE A.Col3=B.Col3;

Upvotes: 2

Related Questions