|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How do I filter out unwanted rows by only one different column?this might be easy but I have no idea how to do it: I have a table like with entires like this: Column1 Column2 Column2 12 name1 user1 13 name2 user2 13 name2 user3 14 name3 user4 15 name4 user5 16 name5 user3 I need a query which shows me as result on a select on this table: 12 name1 user1 13 name2 user2 14 name3 user4 15 name4 user5 16 name5 user3 so the 13 name2 user2a row is filtered out... Thanks for any hints Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in your schema are. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. Just the basics -- like what the heck is the key in that personal, vague pseudo-code you posted? Why do you have two columns with the same name? Maybe you mwant something like this? Your column1 makes no sense, since it permits redundant dat. CREATE TABLE Foobar (vague_name CHAR(10) NOT NULL, vague_user CHAR(10) NOT NULL, PRIMARY KEY (vague_name, vague_user )); >> this might be easy but I have no idea how to do it: << Data integrity is enforced by constraints, and uniqueness is enforcedby UNIQUE/PRIMARY KEY constraints. Is it being filtered out because the 13 in Column1 appears in another
rows? Or because name2 appears twice in the first Column2? or because user3 appears in the second Column2 on another row? Or some combination? Or something else? Roy Show quote On 27 Jul 2006 16:38:06 -0700, "StefanD" <Stefan.Dries***@gmail.com> wrote: >Hello, > >this might be easy but I have no idea how to do it: >I have a table like with entires like this: > >Column1 Column2 Column2 >12 name1 user1 >13 name2 user2 >13 name2 user3 >14 name3 user4 >15 name4 user5 >16 name5 user3 > >I need a query which shows me as result on a select on this table: >12 name1 user1 >13 name2 user2 >14 name3 user4 >15 name4 user5 >16 name5 user3 > >so the 13 name2 user2a row is filtered out... > >Thanks for any hints SELECT *
FROM MyTable WHERE Column1 <> 13 StefanD wrote: Show quote > Hello, > > this might be easy but I have no idea how to do it: > I have a table like with entires like this: > > Column1 Column2 Column2 > 12 name1 user1 > 13 name2 user2 > 13 name2 user3 > 14 name3 user4 > 15 name4 user5 > 16 name5 user3 > > I need a query which shows me as result on a select on this table: > 12 name1 user1 > 13 name2 user2 > 14 name3 user4 > 15 name4 user5 > 16 name5 user3 > > so the 13 name2 user2a row is filtered out... > > Thanks for any hints OK, I see I need to be more specific.
I thought it would be easy with a simple examble. I need to create a view, i cannot work with a table... When I do create this view I get results similar as in this sample... Views to not have the posibility to create unique keys or primary keys as far as I know. So as result I get those entries where 2 columns are the same and one is different. But I cannot work further with these results I NEED one of my result culomns to be unique but my query does give for some multipe results (which is correct!) but I need to filter them out. So as example I posted this "table" which is in fact a view with no primary key or any column specifications. And of course I do not know in advance the results and cannot put a static filter on something... I need something that prevents from showing double entries of one or two rows although the 3rd is different... which doesnt do a simple DISTINCT. I could try to use a 2nd view to filter the first one, but i cannot work with my own tables as the data shown will change and then I would have to keep track and trigger all changes in realtime to be in my table,too, which is an overload. I hope I did describe my issue now better... Thanks already for all replies Stefan
create table #t (c1 int,c2 char(5),c3 char(5)) insert into #t values (12,'name1','user1') insert into #t values (13,'name2','user2') insert into #t values (13,'name2','user3') insert into #t values (14,'name3','user4') insert into #t values (15,'name4','user5') insert into #t values (16,'name5','user6') select * from ( select * ,(select count(*) from #t t where t.c1=#t.c1 and t.c2=#t.c2 and t.c3<=#t.c3) as rnk from #t ) as der where rnk=1 Show quote "StefanD" <Stefan.Dries***@gmail.com> wrote in message news:1154043486.875387.181460@b28g2000cwb.googlegroups.com... > Hello, > > this might be easy but I have no idea how to do it: > I have a table like with entires like this: > > Column1 Column2 Column2 > 12 name1 user1 > 13 name2 user2 > 13 name2 user3 > 14 name3 user4 > 15 name4 user5 > 16 name5 user3 > > I need a query which shows me as result on a select on this table: > 12 name1 user1 > 13 name2 user2 > 14 name3 user4 > 15 name4 user5 > 16 name5 user3 > > so the 13 name2 user2a row is filtered out... > > Thanks for any hints > GREAT! Thanks Uri this does the trick...
Thousand thanks !! Uri Dimant wrote: Show quote > Stefan > create table #t (c1 int,c2 char(5),c3 char(5)) > > insert into #t values (12,'name1','user1') > insert into #t values (13,'name2','user2') > insert into #t values (13,'name2','user3') > insert into #t values (14,'name3','user4') > insert into #t values (15,'name4','user5') > insert into #t values (16,'name5','user6') > > > select * from > ( > select * ,(select count(*) from #t t where t.c1=#t.c1 > and t.c2=#t.c2 and t.c3<=#t.c3) as rnk > from #t > ) as der where rnk=1 > > > > "StefanD" <Stefan.Dries***@gmail.com> wrote in message > news:1154043486.875387.181460@b28g2000cwb.googlegroups.com... > > Hello, > > > > this might be easy but I have no idea how to do it: > > I have a table like with entires like this: > > > > Column1 Column2 Column2 > > 12 name1 user1 > > 13 name2 user2 > > 13 name2 user3 > > 14 name3 user4 > > 15 name4 user5 > > 16 name5 user3 > > > > I need a query which shows me as result on a select on this table: > > 12 name1 user1 > > 13 name2 user2 > > 14 name3 user4 > > 15 name4 user5 > > 16 name5 user3 > > > > so the 13 name2 user2a row is filtered out... > > > > Thanks for any hints > > On 27 Jul 2006 16:38:06 -0700, StefanD wrote:
Show quote >Hello, Hi Stefan,> >this might be easy but I have no idea how to do it: >I have a table like with entires like this: > >Column1 Column2 Column2 >12 name1 user1 >13 name2 user2 >13 name2 user3 >14 name3 user4 >15 name4 user5 >16 name5 user3 > >I need a query which shows me as result on a select on this table: >12 name1 user1 >13 name2 user2 >14 name3 user4 >15 name4 user5 >16 name5 user3 > >so the 13 name2 user2a row is filtered out... > >Thanks for any hints SELECT Column1, Column2, MIN(Column3) FROM YourTable GROUP BY Column1, Column2 -- Hugo Kornelis, SQL Server MVP Even better cause easier Hugo.
Great thanks again. But now... That is a "emergency" solution only... What if I want as result: 12 name1 user1 13 name2 user2, user3 14 name3 user4 15 name4 user5 16 name5 user3 I tried already: SELECT Column1, Column2, Column3= CASE MAX(Column3) = MIN(Column3) THEN MIN(Column3) ELSE MAX(Column3) + ', ' + MIN(Column3) END FROM YourTable GROUP BY Column1, Column2 which works fine for the simplified example above... But in reality there are up to 4 or 5 different user which can appear in Column3 If there is anything further anybody knows... I would be happy, but this here helpled me a lot already. THANKS again Hugo Kornelis wrote: Show quote > On 27 Jul 2006 16:38:06 -0700, StefanD wrote: > > >Hello, > > > >this might be easy but I have no idea how to do it: > >I have a table like with entires like this: > > > >Column1 Column2 Column2 > >12 name1 user1 > >13 name2 user2 > >13 name2 user3 > >14 name3 user4 > >15 name4 user5 > >16 name5 user3 > > > >I need a query which shows me as result on a select on this table: > >12 name1 user1 > >13 name2 user2 > >14 name3 user4 > >15 name4 user5 > >16 name5 user3 > > > >so the 13 name2 user2a row is filtered out... > > > >Thanks for any hints > > Hi Stefan, > > SELECT Column1, Column2, MIN(Column3) > FROM YourTable > GROUP BY Column1, Column2 > > -- > Hugo Kornelis, SQL Server MVP Stefan
I'd like to say that the folowing example isn't really reliable unless it returns a right output. I suggest to perfom such reports on the client side Modify it for your needs create table w ( id int, t varchar(50) ) insert into w values (1,'abc') insert into w values (1,'def') insert into w values (1,'ghi') insert into w values (2,'ABC') insert into w values (2,'DEF') select * from w create function dbo.fn_my ( @id int) returns varchar(100) as begin declare @w varchar(100) set @w='' select @w=@w+t+',' from w where id=@id return @w end select id, dbo.fn_my (dd.id) from ( select distinct id from w ) as dd drop function dbo.fn_my Show quote "StefanD" <Stefan.Dries***@gmail.com> wrote in message news:1154253701.180719.150650@h48g2000cwc.googlegroups.com... > Even better cause easier Hugo. > Great thanks again. > > But now... That is a "emergency" solution only... > What if I want as result: > 12 name1 user1 > 13 name2 user2, user3 > 14 name3 user4 > 15 name4 user5 > 16 name5 user3 > > I tried already: > SELECT Column1, Column2, > Column3= > CASE MAX(Column3) = MIN(Column3) THEN MIN(Column3) > ELSE MAX(Column3) + ', ' + MIN(Column3) > END > FROM YourTable > GROUP BY Column1, Column2 > > which works fine for the simplified example above... > But in reality there are up to 4 or 5 different user which can appear > in Column3 > > If there is anything further anybody knows... I would be happy, but > this here helpled me a lot already. > THANKS again > > Hugo Kornelis wrote: >> On 27 Jul 2006 16:38:06 -0700, StefanD wrote: >> >> >Hello, >> > >> >this might be easy but I have no idea how to do it: >> >I have a table like with entires like this: >> > >> >Column1 Column2 Column2 >> >12 name1 user1 >> >13 name2 user2 >> >13 name2 user3 >> >14 name3 user4 >> >15 name4 user5 >> >16 name5 user3 >> > >> >I need a query which shows me as result on a select on this table: >> >12 name1 user1 >> >13 name2 user2 >> >14 name3 user4 >> >15 name4 user5 >> >16 name5 user3 >> > >> >so the 13 name2 user2a row is filtered out... >> > >> >Thanks for any hints >> >> Hi Stefan, >> >> SELECT Column1, Column2, MIN(Column3) >> FROM YourTable >> GROUP BY Column1, Column2 >> >> -- >> Hugo Kornelis, SQL Server MVP >  
Other interesting topics
|
|||||||||||||||||||||||
