0

Suppose I have the following SQL server table:

enter image description here

I would like the following result, wherein there is only one record per ID and it is the one with the lowest value for field A, but Fields B and C are also included, like so:

enter image description here

How would I accomplish this? Given that group by in sql server disallows the inclusion of fields that are not in the group by clause. I tried a couple things but they all had the same result with an incorrect number of results, the most quintessential being the following:

SELECT a.*
  FROM MyTable as a
  join (SELECT [ID], min ([Field A]) as [Field A] FROM MyTable group by [ID]) as b on a.ID = b.id
  where a.Field A = b.Field A and a.ID = b.ID
  order by OBJECTID

From my understanding the number of results of this should not exceed the number of distinct IDs yet it did, so it must not be doing what I thought it would.

Any help would be much appreciated. Thank you.

大陸北方網友
  • 3,696
  • 3
  • 12
  • 37
Natan Andrews
  • 25
  • 5

2 Answers2

3

SQL Server has a nice TOP 1 WITH TIES trick which may use here:

SELECT TOP 1 WITH TIES [identity], ID, [Field A], [Field B], [Field C]
FROM yourTable
ORDER BY ROW_NUMBER() OVER (PARTITION BY id ORDER BY [Field A];
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

Try this:

WITH DataSource AS
(
    SELECT *
          ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY FiledA ASC) rowID
    FROM MyTable
)
SELECT identity, id, [field a], [field b], [field c]
FROM DataSource
WHERE [rowID] = 1
gotqn
  • 42,737
  • 46
  • 157
  • 243
  • I encountered the same error message as with some of my group by attempts: Column MyTable.Feild B' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Natan Andrews Aug 12 '20 at 05:44