0

I have a sql server table which looks like this:-

Column1 Column2
ABC-123 444
ABC-123 555
ABC-123 666
ABC-222 552
ABC-222 111
ABC-222 552

How do I get data from this table with unique values from Column1 and the smallest value from Column two?

So a valid result for the above data would look like:

Column1 Column2
ABC-123 444
ABC-222 111
Thom A
  • 88,727
  • 11
  • 45
  • 75
Fakhar Ahmad Rasul
  • 1,595
  • 1
  • 19
  • 37
  • 3
    What have you tried so far? – Jens Sep 15 '22 at 15:47
  • @Jens `select DISTINCT Column1, Column2 from MyTable` but this of course does not bring me the smallest value from the second column and that is what I am looking for. – Fakhar Ahmad Rasul Sep 15 '22 at 15:57
  • Presumably you looked at [`MIN`](https://learn.microsoft.com/en-us/sql/t-sql/functions/min-transact-sql?view=sql-server-ver16) so why wasn't that suitable? – Thom A Sep 15 '22 at 16:04

2 Answers2

2
select Column1, min(Column2) from table group by column1

should do the job

Jens
  • 67,715
  • 15
  • 98
  • 113
1

Using WITH TIES

 Select top 1 with ties *
  From  YourTable
  Order By row_number() over (partition by Column1 order by Column2)

Or a Nudge more Performant

;with cte as (
    Select *
          ,RN = row_number() over (partition by Column1 order by Column2) 
      From  YourTable
)
Select *
 From  cte
 Where RN=1
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66