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]
精彩评论