June 21, 2011 at 12:34 pm
I need to select all rows from a table that has the same data in column 1 but based on if specific data is in only one column of the rows.
example below.
I must select all data from column 1 that has 123 based on the fact that one of the matching rows has 999 in column 2.
I cannot figure out how to write the select statement.
column 1 column 2
123 888
123 999
123 888
456 777
June 21, 2011 at 2:01 pm
declare @sample table (column1 int, column2 int)
insert into @sample
select 123, 888 union all
select 123, 999 union all
select 123, 888 union all
select 456, 777
select *
from @sample
where column1 in (select column1 from @sample where column2 = 999)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 21, 2011 at 2:04 pm
CREATE TABLE #T (Col1 INT, Col2 INT) ;
INSERT INTO #T
(Col1, Col2)
VALUES (123, 888),
(123, 999),
(123, 888),
(456, 777) ;
SELECT T1.*
FROM #T AS T1
INNER JOIN #T AS T2
ON T1.Col1 = T2.Col1
WHERE T2.Col2 = 999 ;
Edit: And while I was waiting for my computer to catch up with me, I get beaten to the punch! 🙂
Both solutions do essentially the same thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 21, 2011 at 4:00 pm
the values 888,123 etc... we have about 100,000 distinct values, so that way will not work.
June 21, 2011 at 4:13 pm
npeters 86796 (6/21/2011)
the values 888,123 etc... we have about 100,000 distinct values, so that way will not work.
Yes, it will work. Try it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 21, 2011 at 4:43 pm
Are you looking for something more like this?
declare @sample table (column1 int, column2 int)
declare @work table (col2 int primary key)
insert into @sample
select 123, 888 union all
select 123, 999 union all
select 123, 888 union all
select 456, 777
insert into @work
select distinct column2
from @sample
select col2,s.*
from @work w
cross apply (select * from @sample where column1 in (select column1 from @sample where column2 = col2)) s
order by col2,column1
For performance reasons, I would suggest that your primary table have a nonclustered index on column2,column1.
But the nature of the problem is such that it can only run so fast, if you run it for all values.
Any speed phreaks out there have a better way to approach the problem?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply