January 13, 2016 at 12:14 pm
Hi,
I have below table:
Col1Col2 Col3 RowNumber
123AXC 12/1/2015 1
123AXCVV 12/1/20152
345XSW 12/5/20151
345XSW-SE12/5/20152
567ERW 12/8/20151
Output Needed:
Col1Col2 Col3 RowNumber
123AXC 12/1/2015 1
123AXCVV 12/1/20152
345XSW 12/5/20151
345XSW-SE12/5/20152
January 13, 2016 at 12:52 pm
monilps (1/13/2016)
Hi,I have below table:
Col1Col2 Col3 RowNumber
123AXC 12/1/2015 1
123AXCVV 12/1/20152
345XSW 12/5/20151
345XSW-SE12/5/20152
567ERW 12/8/20151
Output Needed:
Col1Col2 Col3 RowNumber
123AXC 12/1/2015 1
123AXCVV 12/1/20152
345XSW 12/5/20151
345XSW-SE12/5/20152
Quick suggestion
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(Col1,Col2,Col3,RowNumber)
AS (
SELECT 123,'AXC' ,'12/1/2015' ,1 UNION ALL
SELECT 123,'AXCVV' ,'12/1/2015',2 UNION ALL
SELECT 345,'XSW' ,'12/5/2015',1 UNION ALL
SELECT 345,'XSW-SE','12/5/2015',2 UNION ALL
SELECT 567,'ERW' ,'12/8/2015',1
)
,BASE_DATA AS
(
SELECT
SD.Col1
,SD.Col2
,SD.Col3
,SD.RowNumber
,COUNT(*) OVER
(
PARTITION BY SUBSTRING(SD.Col2,1,3)
) AS RCNT
FROM SAMPLE_DATA SD
)
SELECT
BD.Col1
,BD.Col2
,BD.Col3
,BD.RowNumber
FROM BASE_DATA BD
WHERE BD.RCNT = 2;
Results
Col1 Col2 Col3 RowNumber
----------- ------ --------- -----------
123 AXCVV 12/1/2015 2
123 AXC 12/1/2015 1
345 XSW-SE 12/5/2015 2
345 XSW 12/5/2015 1
January 13, 2016 at 1:02 pm
Hi,
I have updated below table:
Col1Col2 Col3 Col4 RowNumber
123AX BEE 12/1/2015 1
123AXCVV BEE 12/1/2015 2
345XSW VE 12/5/2015 1
345XSW VE-SE 12/5/2015 2
567ERWWW XEEE12/8/2015 1
Output Needed:
Col1Col2 Col3 Col4 RowNumber
123AX BEE 12/1/2015 1
123AXCVV BEE 12/1/2015 2
345XSW VE 12/5/2015 1
345XSW VE-SE 12/5/2015 2
January 13, 2016 at 1:10 pm
Isn't this q just a duplicate of http://www.sqlservercentral.com/Forums/Topic1752199-3077-1.aspx
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 13, 2016 at 1:18 pm
monilps (1/13/2016)
Hi,I have updated below table:
Col1Col2 Col3 Col4 RowNumber
123AX BEE 12/1/2015 1
123AXCVV BEE 12/1/2015 2
345XSW VE 12/5/2015 1
345XSW VE-SE 12/5/2015 2
567ERWWW XEEE12/8/2015 1
Output Needed:
Col1Col2 Col3 Col4 RowNumber
123AX BEE 12/1/2015 1
123AXCVV BEE 12/1/2015 2
345XSW VE 12/5/2015 1
345XSW VE-SE 12/5/2015 2
Stop mocking around and spell out the requirements, if you don't understand them then do you're homework.
😎
We are not in the business of guessing or assuming;-)
January 13, 2016 at 1:21 pm
I am sorry about that, later on I just realized it. I will be careful next time.
Didn't meant to waste anyone time.
No, this is similar but not the same. I am trying to achieve how to just pull Row Number 1 and 2 with similar data.
January 13, 2016 at 1:31 pm
monilps (1/13/2016)
I am sorry about that, later on I just realized it. I will be careful next time.Didn't meant to waste anyone time.
No, this is similar but not the same. I am trying to achieve how to just pull Row Number 1 and 2 with similar data.
No worries, won't hold it against you 😀
😎
Here is a slightly altered solution
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(Col1,Col2,Col3,RowNumber)
AS (
SELECT 123,'AXC' ,'12/1/2015' ,1 UNION ALL
SELECT 123,'AXCVV' ,'12/1/2015',2 UNION ALL
SELECT 345,'XSW' ,'12/5/2015',1 UNION ALL
SELECT 345,'XSW-SE','12/5/2015',2 UNION ALL
SELECT 567,'ERW' ,'12/8/2015',1
)
,BASE_DATA AS
(
SELECT
SD.Col1
,SD.Col2
,SD.Col3
,SD.RowNumber
,MAX(SD.RowNumber) OVER
(
PARTITION BY SUBSTRING(SD.Col2,1,3)
) AS RCNT
FROM SAMPLE_DATA SD
)
SELECT
BD.Col1
,BD.Col2
,BD.Col3
,BD.RowNumber
FROM BASE_DATA BD
WHERE BD.RCNT = 2;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply