October 18, 2011 at 9:49 am
Hi,
how i can write a case statement or any other solution from this example that will show me only one row from colb if colb are the same?
THX
create table tbl_test (cola varchar(100),colb varchar(100))
go
insert into tbl_test (cola,colb) values ('first','c:\1')
go
insert into tbl_test (cola,colb) values ('second','c:\1')
go
insert into tbl_test (cola,colb) values ('third','c:\2')
go
October 18, 2011 at 10:04 am
Not sure I understand what you want for output based on your description. If you could explain the desired output we can help put together the sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2011 at 10:09 am
So you want to delete duplicates or something similar?
October 18, 2011 at 11:11 am
i want in the output to remove the duplicate in colb.
October 18, 2011 at 11:16 am
Delete the line or update the column to blank?
October 18, 2011 at 11:23 am
Your explanation is unclear,
If I get you right, you can add up constraint on your table.
--> To add constraint in your table design, to not insert duplicate values
you need to add a unique constraint on the column colb:-
ALTER TABLE tbl_test ADD CONSTRAINT IX_colb UNIQUE(colb)
GO
now, you can't insert duplicate values in the colb .
--> And If you already have duplicate data in table and doesn't want to see them,
;WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY Cola, Colb
ORDER BY ( SELECT 0)) RN
FROM tbl_test)
DELETE FROM cte
WHERE RN > 1
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 18, 2011 at 11:43 am
i want only if colb = 'c:\1' while cola is 'first' and cola 'second' do not show the 'second'.
October 18, 2011 at 11:51 am
Mad-Dog (10/18/2011)
i want only if colb = 'c:\1' while cola is 'first' and cola 'second' do not show the 'second'.
The first and second are actual values in your table or you just made it up ??
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 18, 2011 at 12:17 pm
Mad-Dog (10/18/2011)
Hi,how i can write a case statement or any other solution from this example that will show me only one row from colb if colb are the same?
THX
create table tbl_test (cola varchar(100),colb varchar(100))
go
insert into tbl_test (cola,colb) values ('first','c:\1')
go
insert into tbl_test (cola,colb) values ('second','c:\1')
go
insert into tbl_test (cola,colb) values ('third','c:\2')
go
Just a guess but how about:
SELECT MAX(cola), colb
GROUP BY colb
Thats obviously because 'first', 'second', 'third' happen to sort conveniently into the same order both alphabetically and semantically. Obviously if you want a different order then a case statement would probably help.
If you didn't want a query then my apologies 😛
October 18, 2011 at 2:21 pm
yes they are real but with different names.
October 18, 2011 at 2:28 pm
Well it seems pretty obvious there a number of people willing to help. We have all taken a stab at what it MIGHT be you are looking for. They are all different and apparently none of them are correct. At this point I will ask again for you to explain what you are looking for. Can you tell us the output you would like based on the sample data you provided?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2011 at 2:38 pm
Can you tell us the output you would like to get based on the sample data you provided?
Regards
Sushant Kumar
MCTS,MCP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply