Return a unique row
-
I have a table with a bunch of rows like the below example. The table basicly relates items. I need to return only one row of the relationship. So in the below example I would need to return row 1 or 2 but not both. How can I do this? thanks for your help
col1 col2
row 1 3000025.0 63060.0
row 2 63060.0 3000025.0
-
Select top 1 * From table_name Where something = something
-
This is a tough one. What you really have is unmanaged information. The relationship between the items whether it's col1 and col2 or col2 and col1 should be controlled before entry of the information into the table.
That being said, this should work
select distinct col1, col2 from tablename
union
select distinct col2, col1 from tablename
where cast(col2 as varchar) + cast(col1 as varchar) not in
(select cast(col1 as varchar) + cast(col2 as varchar) from tablename)
If the phone doesn't ring...It's me.
-
OR
Select Distinct Col1, Col2
FROM
(
Select col1, col2 from tblName
UNION ALL
Select col2, col1 from tblName
) Q
HTH
* Noel -
very nice.
If the phone doesn't ring...It's me.
-
what is something = something - col1 = col2?
-
What ever you require for a "where" clause if you need to limit your row set.
-
cowboy's solution will return a single row. I think he misunderstood your question. I would recommend noeld's suggestion. I think it will be a better performing sql, check the execution plan between noel's and mine to know which one to use.
c
If the phone doesn't ring...It's me.
-
wouldn't it be better to simply erase the duplicate rows and adding a unique constraint so that the data is well maintained?
-
btw I don't think Noeld' solution would work... try this :
CREATE TABLE [Test] (
[PKtest] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [int] NOT NULL ,
[Col2] [int] NOT NULL ,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[PKtest]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert into dbo.test (Col1, Col2) values (1, 2)
Insert into dbo.test (Col1, Col2) values (2, 1)
Select Distinct Col1, Col2 from (Select Col1, Col2 from Test
UNION ALL
Select Col2, Col1 from test
) A
Go
drop table test
GO
returns :
1 2
2 1
instead of a single pair... unless I misunderstood the question myself.
-
I know I'm speaking for RonM9 here but I took the problem to mean that the combination the two records of
1 2
2 1
are the same relationship. This is the real problem and I completely agree with you Remi, the data needs to be scrubbed and then a constraint placed on the information.
If the phone doesn't ring...It's me.
-
the solution is simple, but Remi Gregoire just did not tested it's own code.
IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test
CREATE TABLE #Test (
PKtest int IDENTITY (1, 1) NOT NULL ,
Col1 int NOT NULL ,
Col2 int NOT NULL ,
CONSTRAINT PK_Test PRIMARY KEY CLUSTERED ( PKtest )
)
Insert into #test (Col1, Col2) values (1, 2)
Insert into #test (Col1, Col2) values (2, 1)
Select b.PKtest, b.Col1, b.Col2 from (
select max(PKtest) AS PKtest, col1, col2 FROM(
Select PKtest, Col1, Col2 from #Test
UNION ALL
Select PKtest, Col2, Col1 from #test
) A
GROUP BY col1, col2
) a
INNER JOIN #test b on a.PKtest = b.PKtest
GROUP BY b.PKtest, b.Col1, b.Col2
Daniel
-
I tested my code before posting it and it worked as my results show. I never said that the query wasn't possible to create, I was simply stating that noeld's solution didn't work as expected.
On the other hand, the problem still is that the data need to be cleaned. There's just no need to do 2 derived tables and a union and an inner join to query from a simple nn table, both from performance stand point and simply good practice.
-
True, the data need to be cleaned.
And "There's just no need to do 2 derived tables and a union and an inner join to query from a simple nn table, both from performance stand point and simply good practice. " is tru also, but it is the simpliest way. I never tried it on large sized tables, but on 10 - 100k records works acceptable
sometimes. And after all , you only need to use it once : to delete, before the constraint is addedDaniel
-
Would this give the results you desire?
select col1, col2 from test where col1 < col2
union
select col2, col1 from test where col2 <= col1
Still learning, but I think it would do what you are asking.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply