Professional Web Applications Themes

problem with filtering data - MySQL

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" ...

  1. #1

    Default problem with filtering data

    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

    hubmei75@web.de Guest

  2. #2

    Default Re: problem with filtering data

    > 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

    <hubmei75web.de> wrote in message
    news:1137254101.865303.137270g44g2000cwa.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
    >

    Dan Guzman Guest

  3. #3

    Default Re: problem with filtering data

    [email]hubmei75web.de[/email] (hubmei75web.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, [email]esquelsommarskog.se[/email]

    Books Online for SQL Server 2005 at
    [url]http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx[/url]
    Books Online for SQL Server 2000 at
    [url]http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx[/url]
    Erland Sommarskog Guest

  4. #4

    Default Re: problem with filtering data

    [email]hubmei75web.de[/email] 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.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  5. #5

    Default Re: problem with filtering data

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

    -Hubert

    hubmei75@web.de Guest

Similar Threads

  1. Problem filtering Data through Multiple Recordsets
    By Jester_boy in forum Dreamweaver AppDev
    Replies: 5
    Last Post: March 9th, 05:58 PM
  2. Filtering data and adding headers within same datagrid
    By Tr in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: May 20th, 01:26 PM
  3. Filtering data in a Datagrid
    By Steven Cheng[MSFT] in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: April 1st, 02:11 PM
  4. Filtering data in a subform using a combo box
    By Tony Scullion in forum Microsoft Access
    Replies: 0
    Last Post: July 23rd, 12:04 PM
  5. how to extract data filtering by a DateTime field (depending sample period time) ?
    By herve maillard in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 6th, 05:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139