March 23, 2010 at 5:34 am
Hi
Consider this table
Man_name Model
Motorola MotoQ
Motorola RAZR2
Nokia N73
Nokia 6100
Samsung C118
I have used Distinct Statement to retrieve the value.
Now I want to replace if the Man_Name comes 2nd time then I need to replace that value with "Blank".
Is it possible to do this?
March 23, 2010 at 6:17 am
; WITH cteTableName AS
(
SELECTROW_NUMBER() OVER( PARTITION BY Man_name ORDER BY Model ) RowNum, *
FROMTableName
)
SELECTCASE WHEN RowNum > 1 THEN '' ELSE Man_name END Man_name, Model
FROMcteTableName
This should do it for you..
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 23, 2010 at 6:23 am
How do you define which record is the second record? When you have 2 values do you have a rule that according to it you know which one to update and which record to leave as it is? Assuming that you do it by a key, here is a small example of how to do it:
use tempdb
go
declare @tbl table (id int not null primary key, vc varchar(10))
insert into @tbl (id, vc)
select 1, 'aaa'
union
select 2, 'bbb'
union
select 3, 'aaa'
union
select 4, 'aaa'
union
select 5, 'ccc';
with NotToUpdate as(
select MIN(id) as id, vc
from @tbl
group by vc)
update t
set t.vc = ''
from @tbl t left join NotToUpdate NTU on t.id = NTU.id
where NTU.id is null
select * from @tbl
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 23, 2010 at 6:34 am
Hi Kingston,
It is working. Just give me small explanation how this code is working..... If you explain it would be great help.
March 23, 2010 at 6:36 am
You are spliting the data by Partition and assigning row numbers.
After that for row_number> 1 you are assiging blank right.
Is my understanding is correct?
March 23, 2010 at 6:52 am
You are right:-)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 23, 2010 at 7:02 am
Thanks Kingston..
Meet you with another problem.....
Bye!!!
October 2, 2013 at 10:28 pm
Hi Friends,
replace('$filter', 'itime', 'am.itime')
Can any one help with replace command?
This statement is not getting replaced.
Thanks
Shobana
October 2, 2013 at 10:48 pm
Hi,
Here '$filter' word does not contains 'itime' thats why it is not being replaced.
Check below statement for your reference.
select REPLACE('abcdef','ef','EF')
October 2, 2013 at 11:42 pm
Thanks for your immediate reply.................
in $filter they are using conditions like itime>= and itime<=......
select distinct split_part(split_part(cp.msg, 'src=',1),',',1),split_part(split_part(cp.msg, 'user=''',2),',',1) as user ,am.itime,am.dtime,am.url from "FGT60B3908669009-wlog-1380091698" am join "SYSLOG-C0A80A41-glog-1380347892" cp on cast(am.src as character varying)=cast((split_part(split_part(cp.msg, 'src=',2),',',1) ) as character varying) where am.src='192.168.10.23' and replace('$filter', 'itime', 'am.itime')
This is my query......
if the user what 1 day reports it takes long time for querying because of large data..... so i want to replace itime with my am.itime in $ filter............
for more details pls come online in gmail.(shobanapraveen80@gmail.com)
Thanks,
Shobana
October 2, 2013 at 11:54 pm
Instead of using replace use CASE WHEN ... THEN END syntex
like
and $filter = CASE WHEN CONDITION THEN 'VALUE' ELSE 'VALUE' END
October 2, 2013 at 11:55 pm
Instead of using replace use CASE WHEN ... THEN END syntex
like
and $filter = CASE WHEN CONDITION THEN 'VALUE' ELSE 'VALUE' END
October 2, 2013 at 11:59 pm
what this command will do?
whether it gives report for n timestamp(say 1h,one day like that)?
Thanks
Shobana
October 3, 2013 at 12:02 am
With the help of my query can you add your CASE command and show me the query?
Thanks
Shobana
October 3, 2013 at 12:10 am
ERROR: argument of AND must be type boolean, not type text.
LINE 1: ...aracter varying) where am.src='192.168.10.23' and replace('i...
when i run that query i am getting above error...............
its urgent
Can you help me out..............:ermm:
Thanks,
Shobana
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy