help modifying query - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

help modifying query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-14 : 17:56:01

Hi,

I have the following query which works great. However I need to add a bit of functionality to it for a different purpose.

Currently this SPROC selects a list of all distinct cities that users have entered when signing up and filters out all characters that I don't want in there. I order this list alphabetically.

I want to create an option or a second procedure that allows me to order not alphabetically but by popularity of the city which would be the repeating occurences of cities in the database.

Does anyone have any idea how I could do this?

Thanks alot

mike123

CREATE PROCEDURE dbo.select_Cities_Distinct

AS SET NOCOUNT ON

--create tally table
--create table dbo.numbers ( n int primary key)
declare @n int ; set @n = 1
while @n <=255 --max length of input parameter
begin
--insert into numbers
--select @n
set @n = @n + 1
end

select ltrim(City) as city
from
(
select
City ,
case when ((a between 65 and 90) or (a between 97 and 122) or a = 32) then 1 else 0 end as valid
from
(
select City , ascii(substring(City,n,1)) a
from tblUserdetails, numbers
where n <= len(City) and active ='1'
) d1
) d2
where valid = 1 and city not like '%?%'
group by city
having count(valid) = len(city)

order by city

GO

sinapra
Starting Member

24 Posts

Posted - 2004-10-15 : 02:04:37

well how is the popularity being counted, I mean is there any numeric value in the table that puts the priority for the cities!!!

Cheers

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-15 : 02:13:59

Hi sinapra,

What I want to do is order by the most popular cities. So those repeating themselves the most time in the database appear first and the least common appear last. Basically using COUNT I believe.

There is no numeric value in the table, we need to come up with it using the above info I believe.

Thanks!

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-15 : 07:59:18

select city from tblUserdetails
group by city
order by count(*) desc

is this what you are looking for...

Hemanth Gorijala
BI Architect / DBA...
Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-15 : 08:02:19

[code]select city, count(city)
from
(
select top 100 percent of <Your Query>
) d
group by city
order by count(city) desc[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-17 : 21:16:24

Hi Ehorn,

I tried your recommendation and came up with this. Unfortunately it didnt bring back the proper data. All the rows it brought back had a count of 1 or 2 for some odd reason.

hgorijal your query worked fine, but I don't know how I start to integrate it with what I have already.

Thanks alot guys
mike123

alter PROCEDURE dbo.select_Cities_Distinct_MostPopular

AS SET NOCOUNT ON

--create tally table
--create table dbo.numbers ( n int primary key)
declare @n int ; set @n = 1
while @n <=255 --max length of input parameter
begin
--insert into numbers
--select @n
set @n = @n + 1
end

select city, count(city)
from
(

select ltrim(City) as city
from
(
select
City ,
case when ((a between 65 and 90) or (a between 97 and 122) or a = 32) then 1 else 0 end as valid
from
(
select City , ascii(substring(City,n,1)) a
from tblUserdetails, numbers
where n <= len(City) and active ='1'
) d1
) d2
where valid = 1 and city not like '%?%'
group by city
having count(valid) = len(city)

--order by city

) d
group by city
order by count(city) desc

GO

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-17 : 21:58:57

Hmmm...

Can you throw up some sample ddl and dml to demonstrate your situation?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-17 : 22:12:48

Hi Ehorn,

Sure. How does this look?

Thanks again

mike123

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

INSERT INTO tblUserDetails (nameOnline, city) VALUES ('bob1', 'toronto')

INSERT INTO tblUserDetails (nameOnline, city) VALUES ('bob2', 'toronto')

INSERT INTO tblUserDetails (nameOnline, city) VALUES ('bob3', 'seattle')

INSERT INTO tblUserDetails (nameOnline, city) VALUES ('bob4', 'montreal')

I want to bring back something like

City / Count

Toronto / 2
Seattle / 1
Montreal / 1

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-17 : 22:18:19

Thanks Mike,

It would be more useful if it mapped to your original sp above. Otherwise I will end up with a statement resembling Hemanth 's

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-17 : 22:21:19

hi Ehorn,

Sorry what do you mean by mapped. Posted in the same post?

Let me know and I'll do it

Thanks again!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-17 : 22:27:36

I am trying to better understand what you have in your original query above. Based on the data you provided and your intital request - I would end up with a query that looks like the one hgorijal posted.

Can you provide dml and ddl which matches the stuff in your original post - minus the tally table :)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-17 : 22:51:30

Hi Ehorn,

I think what I'm trying to do is have something like hgorijal posted but in this SPROC that filters out the data I don't want, but I'm not 100% sure.

Here is some extra data that shows the reason for the extra lines of code. I'm still not sure if this is what you mean tho, sorry.

Thanks again,
Mike123

INSERT INTO tblUserDetails (nameOnline, city) VALUES ('bob1', 'toronto')

INSERT INTO tblUserDetails (nameOnline, city) VALUES ('bob2', 'toronto')

INSERT INTO tblUserDetails (nameOnline, city) VALUES ('bob3', 'seattle')

INSERT INTO tblUserDetails (nameOnline, city) VALUES ('bob4', 'montreal')

INSERT INTO tblUserDetails (nameOnline, city) VALUES ('bob5', '??99234')

INSERT INTO tblUserDetails (nameOnline, city) VALUES ('bob6', '??%%')

I want to bring back something like

City / Count

Toronto / 2
Seattle / 1
Montreal / 1

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-18 : 00:38:25

Mike,
From you DDL, my query gives you exactly what you want. But you say, you want to integrate it with what you already have? What do you mean by it? I can't quite seem to understand the logic/reason for the code you "already" have. Why don't you give me your exact table structure and actual requirements for the sproc; may be I can help you out.

Hemanth Gorijala
BI Architect / DBA...
Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-18 : 00:59:57

Hi hgorijal,

What I mean is that all the logic in my top SPROC I need to include. With your SPROC it is not filtering out the results like it needs to be filtered.

The reason I have what I have already is to filter out city names that do not adhear to the a-z and 0-9 characters since some have been mistakenly inserted into the db.

Here is my table structure (not exact but I believe everything else is irrelevant)

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

Thanks again!
mike123

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-18 : 01:22:04

considering "filter out city names that do not adhear to the a-z and 0-9 characters " this is ALL that you want to filter out.. add this ....

where patindex('%[^a-zA-Z0-9 ]%', city ) = 0

does this help...

Hemanth Gorijala
BI Architect / DBA...
Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-18 : 02:49:52

hgorijal,

Well I want to filter out exactly what the top SPROC does. I guess I just basically want to insert the

select city from tblUserdetails
group by city
order by count(*) desc

in the appropriate spots but can't plut it in properly

thx
mike123

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-18 : 02:54:48

Mike, let's try it again... Can you explain me, in English, what the "top SPROC" does?

Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-18 : 03:49:01

Hi hgorijal,

Sorry for all the confusion. The top SPROC selects the data I need and filters out bad characters in city names. The only data that is missing from the original query is the count of occurences per each city row.

I did not write the original SPROC, but if you can find a better way that is ok with me. Speed is not very important.

The only thing I need to add to the "top SPROC" is the count of each city occurences in another column and then sort it from most to least.

Thanks again
mike123

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-18 : 04:44:34

Mike,
The output of your query (from your sample data and removeing "active = '1'")is ..
city
---------
seattle
montreal

quote:
having count(valid) = len(city)

Toronto is missing because it exists twice. The above portion of your SQL eliminates any record (city) that exists more than twice.
With this query, you will not have a city, that exists more than once, in the output and so sorting it by popularity can't happen (since "popular" cities are eliminated from your output, you can't sort them)

All the query is trying to do is eliminate any cities with invalid names; but is doing it in a very complex and incorrect way.

The below sqL should be able to replace the entire query and also give you the additional functionality you need.

select city, count(*) as_count from tblUserdetails
where patindex('%[^a-zA-Z0-9 ]%', city ) = 0
group by city order by as_count desc, city asc

Hope it helps...

Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources