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

What index?  Please post table DDL and INSERT statements.

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.

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

Show quoteHide quote

If you want rows including and following (Olson,Dublin)
in (name,city) order, try

select * from yourView
where [name] >= 'Olson'
and ([name] > 'Olson' or [city] >= 'Dublin')

This is equivalent to
where [name] > 'Olson'
or ([name] = 'Olson' and [city] >= 'Dublin')

but it may be more efficient when executed.

Steve Kass
Drew University

<hubme***@web.de> wrote in message
Show quoteHide quote

(comp.databases.mysql removed from groups list)

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

(snip)
Show quoteHide quoteHi 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' )

hubme***@web.de (hubme***@web.de) writes:
Views are by definition unordered. Yes, you can add TOP 100 PERCENT
and ORDER BY, but it does not really mean anything.

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

-Hubert

hubme***@web.de wrote:
Show quoteHide quoteWHERE (name = 'Olson' AND city >= 'Dublin') OR name > 'Olson'

Outer Join and WHERE clause strange behavior
MINUS operation between table date ranges, is my algorithm sound?
generic audit trail best practice questions
append a string to all the values in a column SQL
Extracting from linked tables into pivot