Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Dec 2011
    Posts
    9

    Unanswered: How to Filter same address

    I have this records below, but my problem is how to filter the same address

    Date | Address
    __________________________________________________ ____________
    2011-12-14 6:51:55 AM | 112 Main Street-----|this address must filter and remain one
    2011-12-14 6:52:55 AM | 112 Main Street-----|only according to previous time.
    2011-12-14 6:53:55 AM | 112 Main Street-----|
    2011-12-14 6:57:55 AM | 200 Gambas Ave ----| also this one. remain one only
    2011-12-14 6:58:55 AM | 220 Gambas Ave ----| according to previous time.
    2011-12-14 7:03:56 AM | 01 Highlands Ave -----|
    2011-12-14 7:04:56 AM | 01 Highlands Ave -----| also this one.
    2011-12-14 9:42:19 AM | 230 Center Rd
    2011-12-14 11:12:07 AM | 112 Main Street -----| also this one.
    2011-12-14 11:13:07 AM | 112 Main Street -----|
    2011-12-14 12:38:00 PM | 01 Highlands Ave -----| also this one.
    2011-12-14 12:39:01 PM | 01 Highlands Ave -----|

    OUTPUT should be like this:

    Date | Address
    ________________________________________
    2011-12-14 6:51:55 AM | 112 Main Street
    2011-12-14 6:57:55 AM | 200 Gambas Ave
    2011-12-14 7:03:56 AM | 01 Highlands Ave
    2011-12-14 9:42:19 AM | 230 Center Rd
    2011-12-14 11:12:07 AM | 112 Main Street
    2011-12-14 12:38:00 PM | 01 Highlands Ave
    --------------------------------------------------

    i used GROUP BY(address) here, but the problem is all same address group regardless the time. here is the condition i want to query in mysql:

    if previous address is the same to next address skip don't retrieve the data and if previous address is NOT the same to next address retrieve the data.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try the following:

    SELECT MIN(`Date`) Date, Address
    FROM table
    GROUP BY Address;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Dec 2011
    Posts
    9
    Quote Originally Posted by it-iss.com View Post
    Try the following:

    SELECT MIN(`Date`) Date, Address
    FROM table
    GROUP BY Address;
    this gives me an error

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by haiajavier View Post
    this gives me an error
    please show the actual query you ran (surely your table isn't called "table")

    and for pity's sake, man, show the error message

    we could guess all day, but that would be wasting everybody's time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2011
    Posts
    9
    Hi r937,

    Below is my table created:

    mysql> create table time_address
    -> (id int(10) auto_increment primary key,
    -> date_time datetime,
    -> address char(200));


    +----+---------------------+----------------+
    | id | date_time | address |
    +----+---------------------+----------------+
    | 1 | 2011-12-15 06:52:55 | My Office |
    | 2 | 2011-12-15 06:53:55 | My Office |
    | 3 | 2011-12-15 06:54:55 | My Office |
    | 4 | 2011-12-15 06:55:55 | 5-7 Wood Link |
    | 5 | 2011-12-15 06:56:55 | Wood Avenue 9 |
    | 6 | 2011-12-15 06:57:55 | 200 Gamba Ave |
    | 7 | 2011-12-15 06:58:55 | 200 Gamba Ave |
    | 8 | 2011-12-15 06:59:55 | Gambas Ave |
    | 9 | 2011-12-15 07:00:55 | Wood Avenue 12 |
    | 10 | 2011-12-15 07:01:55 | Wood Avenue 12 |
    | 11 | 2011-12-15 07:02:56 | Wood Avenue 12 |
    | 12 | 2011-12-15 07:03:56 | Star Expy |
    | 13 | 2011-12-15 07:04:56 | Star Expy |
    | 14 | 2011-12-15 07:05:56 | Star Expy |
    | 15 | 2011-12-15 07:06:56 | Wood Avenue 12 |
    | 16 | 2011-12-15 07:07:56 | Wood Avenue 12 |
    | 17 | 2011-12-15 07:08:56 | Wood Avenue 12 |
    | 18 | 2011-12-15 07:10:56 | Star Expy |
    | 19 | 2011-12-15 07:11:56 | Star Expy |
    | 20 | 2011-12-15 11:12:07 | My Office |
    | 21 | 2011-12-15 11:13:07 | My Office |
    | 22 | 2011-12-15 11:14:07 | My Office |
    | 23 | 2011-12-15 11:15:07 | My Office |
    | 24 | 2011-12-15 11:16:07 | My Office |
    +----+---------------------+----------------+

    Output should be like this:

    +----+---------------------+----------------+
    | id | date_time | address |
    +----+---------------------+----------------+
    | 1 | 2011-12-15 06:52:55 | My Office |
    | 4 | 2011-12-15 06:55:55 | 5-7 Wood Link |
    | 5 | 2011-12-15 06:56:55 | Wood Avenue 9 |
    | 6 | 2011-12-15 06:57:55 | 200 Gamba Ave |
    | 8 | 2011-12-15 06:59:55 | Gambas Ave |
    | 9 | 2011-12-15 07:00:55 | Wood Avenue 12 |
    | 12 | 2011-12-15 07:03:56 | Star Expy |
    | 15 | 2011-12-15 07:06:56 | Wood Avenue 12 |
    | 18 | 2011-12-15 07:10:56 | Star Expy |
    | 20 | 2011-12-15 11:12:07 | My Office |
    +----+---------------------+----------------+

    Thanks

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what query did you run?

    and what was the error message?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    This also looks like a different request to your original one.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Dec 2011
    Posts
    13
    Sample data:
    Code:
    insert into time_address values (1,'2011-12-15 06:52:55','My Office'),
    (2,'2011-12-15 06:53:55','My Office'),
    (3,'2011-12-15 06:54:55','My Office'),
    (4,'2011-12-15 06:55:55','5-7 Wood Link'),
    (5,'2011-12-15 06:56:55','Wood Avenue 9'),
    (6,'2011-12-15 06:57:55','200 Gamba Ave'),
    (7,'2011-12-15 06:58:55','200 Gamba Ave'),
    (8,'2011-12-15 06:59:55','Gambas Ave'),
    (9,'2011-12-15 07:00:55','Wood Avenue 12'),
    (10,'2011-12-15 07:01:55',' Wood Avenue 12'),
    (11,'2011-12-15 07:02:56','Wood Avenue 12'),
    (12,'2011-12-15 07:03:56','Star Expy'),
    (13,'2011-12-15 07:04:56','Star Expy'),
    (14,'2011-12-15 07:05:56','Star Expy'),
    (15,'2011-12-15 07:06:56','Wood Avenue 12'),
    (16,'2011-12-15 07:07:56','Wood Avenue 12'),
    (17,'2011-12-15 07:08:56','Wood Avenue 12'),
    (18,'2011-12-15 07:10:56','Star Expy'),
    (19,'2011-12-15 07:11:56','Star Expy'),
    (20,'2011-12-15 11:12:07','My Office'),
    (21,'2011-12-15 11:13:07','My Office'),
    (22,'2011-12-15 11:14:07','My Office'),
    (23 ,'2011-12-15 11:15:07','My Office'),
    (24,'2011-12-15 11:16:07','My Office');
    The query:

    Code:
    SELECT MIN(date_time), address FROM time_address
    GROUP BY address
    And result:

    Code:
    2011-12-15 07:01:55,  Wood Avenue 12
    2011-12-15 06:57:55, 200 Gamba Ave
    2011-12-15 06:55:55, 5-7 Wood Link
    2011-12-15 06:59:55, Gambas Ave
    2011-12-15 06:52:55, My Office
    2011-12-15 07:03:56, Star Expy
    2011-12-15 07:00:55, Wood Avenue 12
    2011-12-15 06:56:55, Wood Avenue 9
    Dmitry

    --
    http://www.sqlines.com - Free online SQL conversion

  9. #9
    Join Date
    Dec 2011
    Posts
    9
    Quote Originally Posted by dm1 View Post
    Sample data:
    Code:
    insert into time_address values (1,'2011-12-15 06:52:55','My Office'),
    (2,'2011-12-15 06:53:55','My Office'),
    (3,'2011-12-15 06:54:55','My Office'),
    (4,'2011-12-15 06:55:55','5-7 Wood Link'),
    (5,'2011-12-15 06:56:55','Wood Avenue 9'),
    (6,'2011-12-15 06:57:55','200 Gamba Ave'),
    (7,'2011-12-15 06:58:55','200 Gamba Ave'),
    (8,'2011-12-15 06:59:55','Gambas Ave'),
    (9,'2011-12-15 07:00:55','Wood Avenue 12'),
    (10,'2011-12-15 07:01:55',' Wood Avenue 12'),
    (11,'2011-12-15 07:02:56','Wood Avenue 12'),
    (12,'2011-12-15 07:03:56','Star Expy'),
    (13,'2011-12-15 07:04:56','Star Expy'),
    (14,'2011-12-15 07:05:56','Star Expy'),
    (15,'2011-12-15 07:06:56','Wood Avenue 12'),
    (16,'2011-12-15 07:07:56','Wood Avenue 12'),
    (17,'2011-12-15 07:08:56','Wood Avenue 12'),
    (18,'2011-12-15 07:10:56','Star Expy'),
    (19,'2011-12-15 07:11:56','Star Expy'),
    (20,'2011-12-15 11:12:07','My Office'),
    (21,'2011-12-15 11:13:07','My Office'),
    (22,'2011-12-15 11:14:07','My Office'),
    (23 ,'2011-12-15 11:15:07','My Office'),
    (24,'2011-12-15 11:16:07','My Office');
    The query:

    Code:
    SELECT MIN(date_time), address FROM time_address
    GROUP BY address
    And result:

    Code:
    2011-12-15 07:01:55,  Wood Avenue 12
    2011-12-15 06:57:55, 200 Gamba Ave
    2011-12-15 06:55:55, 5-7 Wood Link
    2011-12-15 06:59:55, Gambas Ave
    2011-12-15 06:52:55, My Office
    2011-12-15 07:03:56, Star Expy
    2011-12-15 07:00:55, Wood Avenue 12
    2011-12-15 06:56:55, Wood Avenue 9
    Dmitry

    --
    Database and Application Migration - SQLines - Free online SQL conversion

    Hi Dmitry,

    It doesnt look like my expected result, anyway thank you for trying.

    This should be the result:

    +----+---------------------+----------------+
    | id | date_time | address |
    +----+---------------------+----------------+
    | 1 | 2011-12-15 06:52:55 | My Office |
    | 4 | 2011-12-15 06:55:55 | 5-7 Wood Link |
    | 5 | 2011-12-15 06:56:55 | Wood Avenue 9 |
    | 6 | 2011-12-15 06:57:55 | 200 Gamba Ave |
    | 8 | 2011-12-15 06:59:55 | Gambas Ave |
    | 9 | 2011-12-15 07:00:55 | Wood Avenue 12 |
    | 12 | 2011-12-15 07:03:56 | Star Expy |
    | 15 | 2011-12-15 07:06:56 | Wood Avenue 12 |
    | 18 | 2011-12-15 07:10:56 | Star Expy |
    | 20 | 2011-12-15 11:12:07 | My Office |
    +----+---------------------+----------------+

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what query did you run?

    and what was the error message?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2011
    Posts
    9
    Hi r937,

    i run this query,

    SELECT x.id, x.date_time,x.address
    FROM time_address x
    JOIN (select address, MIN(date_time) min_date_time
    FROM time_address
    GROUP BY address) y ON y.address = x.address
    AND y.min_date_time = x.date_time;

    and the result:

    +--+---------------------+----------------+
    id| date_time | address |
    +--+---------------------+----------------+
    1 | 2011-12-15 06:53:55 | My Office |
    4 | 2011-12-15 06:55:55 | 5-7 Wood Link |
    5 | 2011-12-15 06:56:55 | Wood Avenue 9 |
    6 | 2011-12-15 06:57:55 | 200 Gamba Ave |
    8 | 2011-12-15 06:59:55 | Gambas Ave |
    9 | 2011-12-15 07:00:55 | Wood Avenue 12 |
    12 | 2011-12-15 07:03:56 | Star Expy |
    +---------------------+----------------+

    this result was totally eliminate the same address but i am expecting the result below. Does the query for that will be more complex?

    EXPECTED RESULT should be like this:

    +----+---------------------+----------------+
    | id | date_time | address |
    +----+---------------------+----------------+
    | 1 | 2011-12-15 06:52:55 | My Office |
    | 4 | 2011-12-15 06:55:55 | 5-7 Wood Link |
    | 5 | 2011-12-15 06:56:55 | Wood Avenue 9 |
    | 6 | 2011-12-15 06:57:55 | 200 Gamba Ave |
    | 8 | 2011-12-15 06:59:55 | Gambas Ave |
    | 9 | 2011-12-15 07:00:55 | Wood Avenue 12 |
    | 12 | 2011-12-15 07:03:56 | Star Expy |
    | 15 | 2011-12-15 07:06:56 | Wood Avenue 12 |
    | 18 | 2011-12-15 07:10:56 | Star Expy |
    | 20 | 2011-12-15 11:12:07 | My Office |
    +----+---------------------+----------------+

    the expected query should be read as:

    The query will read each row of data based on the address, if the address is the same to next row of data then SKIP else if the address is NOT the same to next row of data then GET the value.

    thanks

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, you have a conceptual problem with the idea of "next"

    there is no such thing as "next" in relational databases, unless it is interpreted as the sequence of values of some column

    your "expected results" contain the same address multiple times

    your real situation doesn't actually involve datetimes and addresses, does it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Dec 2011
    Posts
    9
    Quote Originally Posted by r937 View Post
    first of all, you have a conceptual problem with the idea of "next"

    there is no such thing as "next" in relational databases, unless it is interpreted as the sequence of values of some column

    your "expected results" contain the same address multiple times

    your real situation doesn't actually involve datetimes and addresses, does it
    yes, i just want to query with that result..is any other options?..

  14. #14
    Join Date
    May 2008
    Posts
    17
    get the output ordered by date-tie then do it in you application program program. Would be very easy to do in php or perl.

  15. #15
    Join Date
    Dec 2011
    Posts
    13
    Sorry, the requirement was not clear for me. Try this:

    Code:
     SELECT id, date_time, address
     FROM time_address t
     WHERE address NOT IN 
      ( SELECT address
        FROM time_address
        WHERE id = (SELECT MAX(id) FROM time_address WHERE id < t.id) 
      )
     ORDER BY date_time;
    Result:

    Code:
    '1', '2011-12-15 06:52:55', 'My Office'
    '4', '2011-12-15 06:55:55', '5-7 Wood Link'
    '5', '2011-12-15 06:56:55', 'Wood Avenue 9'
    '6', '2011-12-15 06:57:55', '200 Gamba Ave'
    '8', '2011-12-15 06:59:55', 'Gambas Ave'
    '9', '2011-12-15 07:00:55', 'Wood Avenue 12'
    '12', '2011-12-15 07:03:56', 'Star Expy'
    '15', '2011-12-15 07:06:56', 'Wood Avenue 12'
    '18', '2011-12-15 07:10:56', 'Star Expy'
    '20', '2011-12-15 11:12:07', 'My Office'
    Dmitry

    --
    http://www.sqlines.com - Free online SQL conversion

Posting Permissions

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