September 11, 2014 at 4:38 pm
Hi Experts,
I want to fetch max of Field2 if duplicate records in Field1 and rest of the values of field1 , below is the sample format.
Field1 Field2 Field3 Field4
32 375 abc-xyz A
32 379 xyz-efg A
55 405 abc-xyz B
55 407 xyz-efg B
132 908 abc-xyz C
132 999 xyz-efg C
152 800 abc-xyz D
152 850 xyz-efg D
155 900 abc-xyz E
156 925 abc-xyz F
157 935 abc-xyz G
Thanks,
September 12, 2014 at 3:09 am
I'm not sure I understand what you mean.
Maybe this?
WITH sampleData AS (
SELECT *
FROM (
VALUES
(32, 123, 'test', 'A'),
(32, 124, 'test2', 'A'),
(55, 125, 'test', 'B'),
(55, 126, 'test2', 'B'),
(132, 127, 'test', 'C'),
(132, 128, 'test2', 'C'),
(152, 129, 'test', 'D'),
(152, 130, 'test2', 'D'),
(159, 133, 'tes', 'E'),
(160, 134, 'test', 'F'),
(161, 135, 'test', 'G')
) AS source (Field1,Field2,Field3,Field4)
)
SELECT Field1, Field2 = MAX(Field2) OVER (PARTITION BY Field1), Field3, Field4
FROM sampleData
-- Gianluca Sartori
September 12, 2014 at 6:40 am
DECLARE @temptbl AS TABLE
(
Field1 INT,
Field2 INT,
Field3 VARCHAR(20),
Field4 VARCHAR(10)
)
Insert into @temptbl
select 32,375,'abc-xyz','A' UNION ALL
select 32,379,'xyz-efg','A' UNION ALL
select 55,405,'abc-xyz','B' UNION ALL
select 55,407,'xyz-efg','B' UNION ALL
select 132,908,'abc-xyz','C' UNION ALL
select 132,999,'xyz-efg','C' UNION ALL
select 152,800,'abc-xyz','D' UNION ALL
select 152,850,'xyz-efg','D' UNION ALL
select 155,900,'abc-xyz','E' UNION ALL
select 156,925,'abc-xyz','F' UNION ALL
select 157,935,'abc-xyz','G'
--select * from @temptbl
select * from
(
select *,ROW_NUMBER() OVER(PARTITION BY field1 ORDER BY field2 DESC) Rowno FROM @temptbl
) T
where T.Rowno=1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply