开发者

Merge tables and keep lowest values

Sorry i know merge is something completely different in Sql Server but i couldn't think what else to call it.

I have a User-Defined Table Type which looks like

-------------------------------------------------------------------
|  Id  |  Foreign Key  |  Height  |  Weight  |  Width  |  Length  |
-------------------------------------------------------------------
|  01  |     1256      |   12.2   |   15.8   |   14.5  |    15    |
|  02  |     1256      |   18.2   |   15.8   |   25.8  |    28    |
|  03  |     1258      |   14.5   |   11.3   |   56.6  |    32    |
|  04  |     1258      |   14.5   |   1.85   |   32.9  |    64    |
|  05  |     1216      |   25.3   |   16.2   |   12.5  |    86    |
-------------------------------------------------------------------

And I want to be able to do a query or something that gives me the foreign key with the lowest Height, Weight, Width and Length associated with it so I'd have something like

------------------------------------------------------------
|  Foreign Key  |  Height  |  Weight  |  Width  |  Length  |
------------------------------------------------------------
|     1256      |   12.2   |   15.8   |   14.5  |    15    |
|     1258      |   14.5   |   1.85   |   32.9  |    32    |
|     1216      |   25.3   |   16.2   |   12.5  |    86    |
------------------------------------------------------------

Is there any functions in Sql Server to achieve this, or can any one point me to any resource开发者_开发百科s that may help?

Thanks


Based on your expected output, this should do the trick:

SELECT [Foreign Key], MIN(Height) AS Height, MIN(Weight) AS Weight, 
    MIN(Length) AS Length
FROM @YourTableVar
GROUP BY [Foreign Key]


It is straightforward to select the minimum of a column:

select 
   [Foreign Key], 
   MIN(Height) AS MinHeight, 
   MIN(Weight) AS MinWeight, 
   MIN(Length) AS MinLength
FROM 
   Table
GROUP BY 
   [Foreign Key]
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜