By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,016 Members | 2,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,016 IT Pros & Developers. It's quick & easy.

problem with filtering data

P: n/a
Hello,

I have a simple table containing adresses.
A sample view of the table is

id name city
--------------------------------
100 Meier New York
101 Meier Tokyo
110 Olson Amsterdam
110 Olson Dublin
111 Paul Berlin
...

The view is ordered by (name,city)

Now my problem:

I want to see only the second half of the view
starting from "Olson" in "Dublin"

Using the constraint

where (name>="Olson") and (city>="Dublin")

does not the right thing. It eliminates i.e. row 111.
The only idea I have is to concatenate the fields to
simulate the compound index to be able to do

where name+"~"+city>="Olson~Dublin"

not to forget to struggle with null fields to get
the right results. This slows down the simple query
dramatically.

This seems to be a complicated solution for a simple
problem, given the fact, that the index is already
available at the server.

Does anyone has an idea or suggestion?

-Hubert

Jan 14 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
> This seems to be a complicated solution for a simple
problem, given the fact, that the index is already
available at the server.
What index? Please post table DDL and INSERT statements.
The view is ordered by (name,city)
Is name/city unique? If not, you should add ID to the ORDER BY and your
criteria so that you can skip rows with identical values in those columns.
not to forget to struggle with null fields to get
the right results. This slows down the simple query
dramatically.
The example below should perform well with proper indexing. The column
value concatenation method prevents the efficient use of indexes.

CREATE TABLE Addresses
(
ID int NOT NULL
CONSTRAINT PK_Addresses PRIMARY KEY ,
Name varchar(20) NULL,
City varchar(20) NULL
)

ALTER TABLE Addresses
ADD CONSTRAINT UQ_Addresses UNIQUE (Name, City)

INSERT INTO Addresses SELECT 100, 'Meier', 'New York'
UNION ALL SELECT 101, 'Meier', 'Tokyo'
UNION ALL SELECT 110, 'Olson', 'Amsterdam'
UNION ALL SELECT 110, 'Olson', 'Dublin'
UNION ALL SELECT 111, 'Paul', 'Berlin'
UNION ALL SELECT 200, NULL, NULL
UNION ALL SELECT 201, 'n', NULL
UNION ALL SELECT 202, NULL, 'b'
GO

DECLARE @Name varchar(20)
DECLARE @City varchar(20)

SET @Name = 'Olson'
SET @City = 'Dublin'

SELECT ID, Name, Address
FROM Addresses
WHERE
(Name > @Name OR (@Name IS NULL AND Name IS NOT NULL)) OR
((Name = @Name OR (Name IS NULL AND @Name IS NULL)) AND
(City >= @City OR
(@City IS NULL AND City IS NULL) OR
(@City IS NULL AND City IS NOT NULL)))
ORDER BY Name, City

--
Hope this helps.

Dan Guzman
SQL Server MVP

<hu******@web.de> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com... Hello,

I have a simple table containing adresses.
A sample view of the table is

id name city
--------------------------------
100 Meier New York
101 Meier Tokyo
110 Olson Amsterdam
110 Olson Dublin
111 Paul Berlin
...

The view is ordered by (name,city)

Now my problem:

I want to see only the second half of the view
starting from "Olson" in "Dublin"

Using the constraint

where (name>="Olson") and (city>="Dublin")

does not the right thing. It eliminates i.e. row 111.
The only idea I have is to concatenate the fields to
simulate the compound index to be able to do

where name+"~"+city>="Olson~Dublin"

not to forget to struggle with null fields to get
the right results. This slows down the simple query
dramatically.

This seems to be a complicated solution for a simple
problem, given the fact, that the index is already
available at the server.

Does anyone has an idea or suggestion?

-Hubert

Jan 14 '06 #2

P: n/a
(comp.databases.mysql removed from groups list)

On 14 Jan 2006 07:55:01 -0800, hu******@web.de wrote:

(snip)
The only idea I have is to concatenate the fields to
simulate the compound index to be able to do

where name+"~"+city>="Olson~Dublin"

not to forget to struggle with null fields to get
the right results. This slows down the simple query
dramatically.

This seems to be a complicated solution for a simple
problem, given the fact, that the index is already
available at the server.

Does anyone has an idea or suggestion?


Hi Hubert,

Here's a version that makes optimal use of existing indexes on name or
name + city columns:

WHERE name >= 'Olson'
AND ( name > 'Olson OR city > 'Dublin' )

--
Hugo Kornelis, SQL Server MVP
Jan 14 '06 #3

P: n/a
hu******@web.de (hu******@web.de) writes:
The view is ordered by (name,city)


Views are by definition unordered. Yes, you can add TOP 100 PERCENT
and ORDER BY, but it does not really mean anything.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 14 '06 #4

P: n/a
hu******@web.de wrote:
Hello,

I have a simple table containing adresses.
A sample view of the table is

id name city
--------------------------------
100 Meier New York
101 Meier Tokyo
110 Olson Amsterdam
110 Olson Dublin
111 Paul Berlin
...

The view is ordered by (name,city)

Now my problem:

I want to see only the second half of the view
starting from "Olson" in "Dublin"

Using the constraint

where (name>="Olson") and (city>="Dublin")

does not the right thing. It eliminates i.e. row 111.
The only idea I have is to concatenate the fields to
simulate the compound index to be able to do

where name+"~"+city>="Olson~Dublin"

not to forget to struggle with null fields to get
the right results. This slows down the simple query
dramatically.

This seems to be a complicated solution for a simple
problem, given the fact, that the index is already
available at the server.

Does anyone has an idea or suggestion?

-Hubert


WHERE (name = 'Olson' AND city >= 'Dublin') OR name > 'Olson'

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Jan 15 '06 #5

P: n/a
Thanks to all! You helped me very much.
Sometimes I am thinking too complicated
to see the straight solutions :)

-Hubert

Jan 15 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.