Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 3817
Home > Forums > Sql Server >

Help Required on Sql Query

Posted by Pavanandey under Sql Server on 12/27/2012 | Points: 10 | Views : 2218 | Status : [Member] | Replies : 18
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
Query Segment
----------------------------------------------------------
act of kindness local
act of kindness PS
adam levine celebrities
address book tech
adp finance
adrienne maloof celebrities
adrienne maloof divorce celebrities
amazon local
amazon tech

i have my date as above.
here Query 'act of kindness' belongs to two segment 'local' and 'PS' amd simaialry amazon also belongs to two segments.
by out put should be these two entries

Query
---------------------------
act of kindness
amazon

Thanks
Pavan Kumar
Mark Answer if this fits the need

Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Pavanandey on: 12/27/2012 [Member] Bronze | Points: 25

Up
0
Down
Query --->Segment
****************************************
act of kindness --->local
act of kindness --->PS
adam levine --->celebrities
address book --->tech
adp --->finance
adrienne maloof --->celebrities
adrienne maloof divorce --->celebrities
amazon --->local
amazon --->tech

Thanks
Pavan Kumar
Mark Answer if this fits the need

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Kirthiga on: 12/27/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Pavan Kumar,

Try this below query 

select Query from [TableName] group by Query


Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Pavanandey on: 12/28/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi @Kirthiga please check the query again

Thanks
Pavan Kumar
Mark Answer if this fits the need

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Pavanandey on: 12/28/2012 [Member] Bronze | Points: 25

Up
0
Down
i need the queries with more then one segment

Thanks
Pavan Kumar
Mark Answer if this fits the need

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Kirthiga on: 12/28/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Pavan Kumar,

I think this will give queries with more than one segment 

select Query from (

select Query,ROW_NUMBER()over(partition by Query order by Query)Rowno from [TableName]

)a where Rowno>1


Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Pavanandey on: 12/28/2012 [Member] Bronze | Points: 25

Up
0
Down
its not working @Kirthiga

Thanks
Pavan Kumar
Mark Answer if this fits the need

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Ranjeet_8 on: 12/28/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
Hi @Pavanandey
Try this 

SELECT     REPLACE(REPLACE(COLUMN_Name, 'local', ''), 'PS', '') AS Expr1

FROM         TABLE_Name

WHERE     (COLUMN_Name LIKE '%local') OR  (COLUMN_Name LIKE '%PS')


Please Mark As Answer if this helps you

.

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Pavanandey on: 12/28/2012 [Member] Bronze | Points: 25

Up
0
Down
@Ranjeet_8
the one you have given is not working
and more over the segments like local,PS are not fixed. there are around 10000 segments

Thanks
Pavan Kumar
Mark Answer if this fits the need

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Kirthiga on: 12/28/2012 [Member] Starter | Points: 25

Up
0
Down
@Pavan

can you tell what is the issue you faced on my query i try to correct it

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Pavanandey on: 12/28/2012 [Member] Bronze | Points: 25

Up
0
Down
@Kirthiga

select Query from (
select Query,ROW_NUMBER()over(partition by Query order by Query)Rowno from [TableName]
)a where Rowno>1

i need queries having more then one segment... you havent mentioned atleast segment in the query

Thanks
Pavan Kumar
Mark Answer if this fits the need

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sourabh07 on: 12/28/2012 [Member] Starter | Points: 25

Up
0
Down
hi..

try the following query.... 

With cust as (

	Select ROW_NUMBER() over (partition by Query order by Query)

	as rcnt,Query,Segment from @temp

) select distinct Query from cust where rcnt>1


Sourabh07

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Pavanandey on: 12/28/2012 [Member] Bronze | Points: 25

Up
0
Down
@Sourabh07
'2012-2013 football bowl games' == 'health'
'2012-2013 football bowl games' == 'sports'
'2012-2013 football bowl games' == 'sports'
'amazon' --->'local'
'amazon' --->'tech'

here '2012-2013 football bowl games' belong to sports twice and health once. i am getting ''2012-2013 football bowl games' as ouput since it belongs to sports twice.

but i need output as
'2012-2013 football bowl games' 'health'
'2012-2013 football bowl games' == 'sports'
'amazon' --->'local'
'amazon' --->'tech'


Thanks
Pavan Kumar
Mark Answer if this fits the need

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sourabh07 on: 12/28/2012 [Member] Starter | Points: 25

Up
0
Down
hi..

try this.. 

Declare @temp2 table

(

	Query varchar(100)

);



With cust as (

	Select ROW_NUMBER() over (partition by Query order by Query)

	as rcnt,Query,Segment from Table_name

) insert into @temp2(Query) 

select distinct Query from cust where rcnt>1;



select distinct Segment,Query from Table_name

where Query in (select Query from @temp2)


Sourabh07

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Pavanandey on: 12/28/2012 [Member] Bronze | Points: 25

Up
0
Down
@Sourabh07
i am getting querys which are having one segment also

Thanks
Pavan Kumar
Mark Answer if this fits the need

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sourabh07 on: 12/28/2012 [Member] Starter | Points: 25

Up
0
Down
hi..

did you get an answer.....??

Sourabh07

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Pavanandey on: 12/28/2012 [Member] Bronze | Points: 25

Up
0
Down
@Sourabh07
i am getting querys which are having one segment also

Thanks
Pavan Kumar
Mark Answer if this fits the need

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Pavanandey on: 12/28/2012 [Member] Bronze | Points: 25

Up
0
Down
I NEED QUERIES WHICH ARE HAVING MORE THEN ONE SEGMENT

Thanks
Pavan Kumar
Mark Answer if this fits the need

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sourabh07 on: 12/28/2012 [Member] Starter | Points: 25

Up
0
Down
hi..

no issue....

the code i have posted second time might be meet your requirement...

if not..then please tell me.....on which condition it fails....

as I am getting the same output which was posted by you earlier....

try it out....

Sourabh07

Pavanandey, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts