July 21, 2008 at 8:22 am
I have a table with the following structure:
REF Id Value Code
-------------------- ----------- ----------- ---------
169 176810994 0 X
169 176812280 0 X
169 176812364 0 X
169 180265820 0 X
169 180535546 0 X
169 180580090 0 X
169 140776516 14 X
2757 99375175 0 X
2757 125645505 0 X
2757 132860208 0 X
2757 136065800 33 F
2757 135375692 38 F
how would I select the REF, Id and Max(Value) and code for grouped on REF i.e
2 records would be returned:
REF Id Value Code
-------------------- ----------- ----------- ---------
169 140776516 14 X
2757 135375692 38 F
July 21, 2008 at 8:36 am
Is this what you're looking for?
DECLARE @TABLE TABLE
(
[REF] INT NOT NULL,
[Id] BIGINT NOT NULL,
[Value] INT NOT NULL,
[Code] CHAR(1) NOT NULL
)
INSERT INTO @TABLE([Ref],[ID],[Value],[Code])
SELECT 169, 176810994, 0, 'X' UNION
SELECT 169, 176812280, 0, 'X' UNION
SELECT 169, 176812364, 0, 'X' UNION
SELECT 169, 180265820, 0, 'X' UNION
SELECT 169, 180535546, 0, 'X' UNION
SELECT 169, 180580090, 0, 'X' UNION
SELECT 169, 140776516, 14, 'X' UNION
SELECT 2757, 99375175, 0, 'X' UNION
SELECT 2757, 125645505, 0, 'X' UNION
SELECT 2757, 132860208, 0, 'X' UNION
SELECT 2757, 136065800, 33, 'F' UNION
SELECT 2757, 135375692, 38, 'F'
SELECT T.Ref, T.ID, T.Value, T.Code FROM @Table T
INNER JOIN
(
SELECT Ref, MAX(Value) AS MaxValue FROM @Table GROUP BY Ref
) AS D ON T.Ref = D.Ref AND T.Value = D.MaxValue
ORDER BY Ref, ID ASC
Results:
16914077651614X
275713537569238F
Sorry its a little sketchy (May not cope well with ties & dupes), late night last night driving home from up North 🙂
July 21, 2008 at 8:51 am
Excellent cheers exactly what i was after. Thanks for the help 😛
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply