August 24, 2012 at 6:59 am
I have a table:
D12341001
A11224001
D12342001
C13425001
B19646001
A11223001
you can see leftside values D1234 and A1122 are repeated. Now I want the result as very first numerical ID returned when I meet the alphanumeric code first time. Result should be:
D12341001
A11224001
C13425001
B19646001
August 24, 2012 at 7:01 am
ganeshkumar005 (8/24/2012)
I have a table:D12341001
A11224001
D12342001
C13425001
B19646001
A11223001
you can see leftside values D1234 and A1122 are repeated. Now I want the result as very first numerical ID returned when I meet the alphanumeric code first time. Result should be:
D12341001
A11224001
C13425001
B19646001
What do you mean by 'first'?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 24, 2012 at 7:08 am
for 'D1234', the first time it appears while reading from top, the value against it is 1001. I want this row in result. And skip the others if 'D1234' comes again.
I mean distinct values from one column with a value from second colum too.
August 24, 2012 at 7:14 am
ganeshkumar005 (8/24/2012)
for 'D1234', the first time it appears while reading from top, the value against it is 1001. I want this row in result. And skip the others if 'D1234' comes again.I mean distinct values from one column with a value from second colum too.
This works for your sample data:
WITH cte_test AS
(
SELECT
RID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
,Label
,Value
FROM dbo.test
)
SELECT * FROM
(
SELECT
Label
,Value
,RID2 = ROW_NUMBER() OVER (PARTITION BY Label ORDER BY RID)
FROM cte_test
) tmp
WHERE RID2 = 1;
However, please note there isn't such a thing as "the first time it appears while reading from top" in a relation table. Rows do not have a predefined order. I mimicked the ordering by using SELECT NULL in the OVER clause, but there's actually no garantuee it will always give the same result.
If you do want this, you'll need to add a primary key to your table and sort against this primary key. (something I mimicked by adding a rownumber in the CTE).
Also please read the first link in my signature on how to ask questions in this forum. Specifically on how to post DDL and sample data.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 24, 2012 at 7:16 am
ganeshkumar005 (8/24/2012)
for 'D1234', the first time it appears while reading from top, the value against it is 1001. I want this row in result. And skip the others if 'D1234' comes again.I mean distinct values from one column with a value from second colum too.
'Reading from the top'? But there is no default ORDER BY on a table, so this does not mean anything.
So ... select x from table might return rows in a certain order for a few months, but this could easily change one day when SQL Server calculates a different execution plan.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 24, 2012 at 7:20 am
Thanks for your reply. 'reading from the top' is not a concern. My purpose was to elaborate the problem. I will verify your code.
See you in part 2. This is a requirement, not game. 🙂
August 24, 2012 at 7:22 am
ganeshkumar005 (8/24/2012)
I have a table:D12341001
A11224001
D12342001
C13425001
B19646001
A11223001
you can see leftside values D1234 and A1122 are repeated. Now I want the result as very first numerical ID returned when I meet the alphanumeric code first time. Result should be:
D12341001
A11224001
C13425001
B19646001
Doe this represent your table correctly?
DECLARE @Table TABLE(Col1 varchar(20),Col2 varchar(20));
INSERT INTO @Table (Col1, Col2) VALUES('D1234','1001');
INSERT INTO @Table (Col1, Col2) VALUES('A1122','4001');
INSERT INTO @Table (Col1, Col2) VALUES('D1234','2001');
INSERT INTO @Table (Col1, Col2) VALUES('C1342','5001');
INSERT INTO @Table (Col1, Col2) VALUES('B1964','6001');
INSERT INTO @Table (Col1, Col2) VALUES('A1122','3001');
SELECT * FROM @Table;
Col1 Col2
D12341001
A11224001
D12342001
C13425001
B19646001
A11223001
August 24, 2012 at 7:25 am
Koen Verbeeck (8/24/2012)
ganeshkumar005 (8/24/2012)
for 'D1234', the first time it appears while reading from top, the value against it is 1001. I want this row in result. And skip the others if 'D1234' comes again.I mean distinct values from one column with a value from second colum too.
This works for your sample data:
WITH cte_test AS(
SELECT
RID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
,Label
,Value
FROM dbo.test
)
SELECT * FROM
(
SELECT
Label
,Value
,RID2 = ROW_NUMBER() OVER (PARTITION BY Label ORDER BY RID)
FROM cte_test
) tmp
WHERE RID2 = 1;
However, please note there isn't such a thing as "the first time it appears while reading from top" in a relation table. Rows do not have a predefined order. I mimicked the ordering by using SELECT NULL in the OVER clause, but there's actually no garantuee it will always give the same result.
If you do want this, you'll need to add a primary key to your table and sort against this primary key. (something I mimicked by adding a rownumber in the CTE).
Also please read the first link in my signature on how to ask questions in this forum. Specifically on how to post DDL and sample data.
Here is the above applied to the sample table
DECLARE @Table TABLE(Col1 varchar(20),Col2 varchar(20));
INSERT INTO @Table (Col1, Col2) VALUES('D1234','1001');
INSERT INTO @Table (Col1, Col2) VALUES('A1122','4001');
INSERT INTO @Table (Col1, Col2) VALUES('D1234','2001');
INSERT INTO @Table (Col1, Col2) VALUES('C1342','5001');
INSERT INTO @Table (Col1, Col2) VALUES('B1964','6001');
INSERT INTO @Table (Col1, Col2) VALUES('A1122','3001');
WITH cte_test AS
(
SELECT
RID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
,Col1 as Label
,Col2 as Value
FROM @Table
)
SELECT * FROM
(
SELECT
Label
,Value
,RID2 = ROW_NUMBER() OVER (PARTITION BY Label ORDER BY RID)
FROM cte_test
) tmp
WHERE RID2 = 1;
Label Value RID2
-------------------- -------------------- --------------------
A1122 4001 1
B1964 6001 1
C1342 5001 1
D1234 1001 1
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply