help with query (group by modified column)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with query (group by modified column)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-18 : 13:44:07

Hi,

Having trouble writing this query. Basically it is a modified version of this query:

SELECT query,count(*) AS queryCount FROM tblSE_referrerDetails
WHERE DateDiff(dd, queryDate, GetDate()) < 15
GROUP BY query ORDER BY queryCount DESC

In this table, we have a "query" column, and there we have many terms with city names appended. For example

#1.) "new york" + term1
#2.) "london" + term1
#3.) "tokyo" + term1

I have a list of city names accessible by querying :
SELECT DISTINCT (city) FROM userDetails"

What I want to do is modify all the "query" column values and strip them of the city names, so that the above example would look like this

#1.) term1
#2.) term1
#3.) term1

Perhaps I am just running a replace function on the terms ?

I'm really not sure how to structure this query, and any help would be greatly appreciated.

Thanks again! :),
mike123

table structures below :

CREATE TABLE [dbo].[tblSE_ReferrerDetails](
[queryID] [int] IDENTITY(1,1) NOT NULL,
[referrerURL] [varchar](1000) NULL,
[query] [varchar](1000) NULL,
[landingPage] [varchar](1000) NULL,
[queryDate] [datetime] NULL,
[IP] [varchar](15) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tblUserDetails](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL,
[City] [varchar](25) NULL
)

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 13:46:53

just do select substring(query,charindex('+',query)+1,len(query)) from table

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-18 : 13:57:03

Hi Visakh16,

I'm not following you exactly. For each row I want to bring back I want to filter it against "SELECT DISTINCT(city) FROM tblUserDetails"

Don't I have to integrate that in there somehow ? Am I missing it ?

Thanks again,
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 14:01:23

quote:
Originally posted by mike123

Hi Visakh16,

I'm not following you exactly. For each row I want to bring back I want to filter it against "SELECT DISTINCT(city) FROM tblUserDetails"

Don't I have to integrate that in there somehow ? Am I missing it ?

Thanks again,
mike123

but what you told in last post was to get query column value stripped off city names to have only term1 in it. thats what query i gave toy does. do you mean you need to use that value to query something?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-19 : 14:34:36

Or simply join on a like condition?

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-19 : 16:33:40

quote:

but what you told in last post was to get query column value stripped off city names to have only term1 in it. thats what query i gave toy does. do you mean you need to use that value to query something?

yes this is correct. Perhaps I am just not interpretting it properly. I don't see how what you wrote relates to "SELECT DISTINCT(city) FROM tblUserDetails" Since we want to filter the city names.

Please keep in mind that the queries aren't always in this order of cityname first then random term.

For example we could have terms like

london restaurants
restaurants london
places to eat in london

We want this converted to

restaurants
restaurants
places to eat in

So the final query will bring back something like

term / count

restaurants / 2
places to eat in / 1

Hopefully this makes things a bit more clear ? Please let me know if thats what you are already referring to. I think I'm missing a piece of the puzzle here.

Thanks again!
Mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-19 : 16:35:51

quote:
Originally posted by Peso

Or simply join on a like condition?

E 12°55'05.63"
N 56°04'39.26"

hopefully my last post adds some clarity to this situation? JOIN on like perhaps is what I am looking for but I can't really picture the approach here..

Thanks again!
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-19 : 23:59:36

[code]declare @test table
(
id int,
strval varchar(1000)
)

declare @test1 table
(
id int,
strval varchar(1000)
)
insert into @test
select 1,'london restaurants' union all
select 2,'restaurants london' union all
select 3,'places to eat in london' union all
select 4,'restaurants & places to visit in india' union all
select 5,'venice in banks of river' union all
select 6,'church in vatican'

insert into @test1
select 1,'london' union all
select 2,'vatican' union all
select 3,'india' union all
select 4,'venice' union all
select 5,'paris'

select replace(t.strval,t1.strval,'')
from @test t
join @test1 t1
on ' ' + t.strval + ' ' like '% '+ t1.strval + ' %'

output
-----------------------------------------
restaurants
restaurants
places to eat in
church in
restaurants & places to visit in
in banks of river[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-20 : 08:11:37

Hi Visakh16,

Your sample code is pretty much exactly what I am looking for. (I will just slightly modify to add a group by function, hopefully this doesnt mess things up)

The problem I am having is when I integrate it with my actual code, the desired results are not returned. Perhaps its to do with my integration, but I don't think so?

I am running the following code, and its bringing back 104 records for some reason. It should be brining back results that look the same as your sample code. (we have all locations you used in this table).

I can't determine why 104 records are always brought back ? There are 2,187,464 rows in the [tblGeodb_worldCities] table.

Any suggestions greatly appreciated!!

thanks once again :)
mike123

declare @test table
(

strval varchar(1000)
)

declare @test1 table
(

strval varchar(1000)
)
insert into @test
select 'london restaurants' union all
select 'restaurants london' union all
select 'places to eat in london' union all
select 'restaurants & places to visit in india' union all
select 'venice in banks of river' union all
select 'church in vatican'

insert into @test1
SELECT [cityName]
FROM [dbo].[tblGeodb_worldCities]

select replace(t.strval,t1.strval,'')
from @test t
join @test1 t1
on ' ' + t.strval + ' ' like '% '+ t1.strval + ' %'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 08:15:06

I'm asuming that you'll always have city/country names coming as independent words. is this true in yuour case?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-20 : 08:30:42

what do you mean exactly by independant words ? the text values are user generated searches, so I don't have control over that and theres no pattern.... not sure if that answers your question ? =\

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 08:34:24

quote:
Originally posted by mike123

what do you mean exactly by independant words ? the text values are user generated searches, so I don't have control over that and theres no pattern.... not sure if that answers your question ? =

i meant like 'restaurants in india' where india appears as a seperate word and not like 'indian restaurants'.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-20 : 08:36:33

yes, they will all be independant words..... since "indian" is not a city/state/province/country , it should be treated as a regular word that has no relation to a region, or more specifically no relation to "india"

Thanks again!
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 08:38:34

then it should work fine.Are there sentences without city names at all? then use LEFT JOIN instead of INNER JOIN.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-20 : 09:14:26

Hi Visakh16,

I changed

SELECT [cityName] FROM [dbo].[tblGeodb_worldCities]

to

SELECT distinct(cityName) FROM [dbo].[tblGeodb_worldCities] WHERE cityname <> 'in'

and the results have improved, but not yet perfect. We were getting too many records before because of duplicate city names. (there are 12 londons in the world apparently! )

Notice the empty spaces in the results brought back and also how I have exclued the city "IN". Hopefuly there aren't too many words like this in the database. I guess I have no choice but to find them manually and exclude them.

I can't figure out why the results brought back below are brought back the way they are and what could be causing it.

any suggestions greatly appreciated, once again! :)

mike123

restaurants
restaurants
places to eat in
places eat in london
restaurants & places visit in india
restaurants & places to visit in
venice in banks river
in banks of river
venice in banks of
venice in of river
church in
in vatican

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-20 : 09:35:11

Is the city name always the first or last word in the sentence?

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-20 : 09:42:09

quote:
Originally posted by Peso

Is the city name always the first or last word in the sentence?

E 12°55'05.63"
N 56°04'39.26"

unfortunately not... I don't have any control over the user input, there are no consistencies =
thanks,
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-20 : 09:52:55

[code]declare @test table
(
id int,
strval varchar(1000)
)

declare @test1 table
(
id int,
strval varchar(1000)
)

insert into @test
select 1,'london restaurants' union all
select 2,'restaurants london' union all
select 3,'places to eat in london' union all
select 4,'restaurants & places to visit in india' union all
select 5,'venice in banks of river' union all
select 6,'church in vatican'

insert into @test1
select 1,'london' union all
select 2,'vatican' union all
select 3,'india' union all
select 4,'venice' union all
select 5,'paris'

;WITH Yak (ID, rowID, Data)
AS (
SELECT t.id,
g.RowID,
g.data
FROM @test AS t
CROSS APPLY dbo.fnParseList(' ', t.strval) AS g
LEFT JOIN @test1 AS w ON w.strval = g.Data
WHERE w.id IS NULL
)

SELECT i.ID,
STUFF(w.f, 1, 1, '')
FROM (
SELECT ID
FROM Yak
GROUP BY ID
) AS i
CROSS APPLY (
SELECT ' ' + y.Data
FROM Yak AS y
WHERE y.ID = i.ID
AND y.Data NOT IN ('in', 'to')
ORDER BY y.rowID
FOR XML PATH('')
) AS w(f)[/code]

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-20 : 10:27:34

Hey Peso,

Looks great, but having problem executing because of a missing "dbo.fnParseList" Would you be able to provide ? :)

thanks again!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-20 : 10:40:12

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033
Use second version further down.

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-20 : 11:25:30

Hey Peso,

This looks great. I am just wondering is it possible to get a GROUP BY clause on a query structured this way ?

Your results look like

1 restaurants
2 restaurants
3 places to eat in
4 restaurants & places to visit in
5 in banks of river
6 church in

Also I was thinking of removing the ID column ? I tried but have failed so far.. Does it need to be in there ? ( I dont have a need for passing it)

If its possible to have it like below ? Im unfamiliar with a few commands used here and don't want to mess it up.

restaurants (2)
places to eat in (1)
restaurants & places to visit in (1)
in banks of river (1)
church in (1)

Thanks again!
mike123

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources