Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Question Unanswered: Select Distinct On Concated Fields

    Hi all!

    I try to make a query that select only the distinct values of two combined fields.
    I'd tried a couple of things but none of them work.

    Here's an example of the data:

    Code:
    IP_ADDRESS | _DATE       | MORE COLLUMNS...
    127.0.0.1    | 20070101   | MORE DATA
    192.168.0.1 | 20070101   | MORE DATA
    127.0.0.1    | 20070101   | MORE DATA
    127.0.0.1    | 20070101   | MORE DATA
    192.168.0.2 | 20070101   | MORE DATA
    192.168.0.1 | 20070102   | MORE DATA
    127.0.0.1    | 20070102   | MORE DATA
    I need to get unique visits out of this table so I tried this:

    Code:
    SELECT DISTINCT(CONCAT(IP_ADDRESS, _DATE)), MORE_COLUMNS, _DATE FROM myTable
    But it doesn't select only distinct values of CONCAT(IP_ADDRESS,_DATE)
    it returns all the rows.

    Please help!

    Regards

    M. Ortho
    Last edited by ortho; 09-20-07 at 12:08.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    A common misconception...
    DISTINCT is applied to all selected rows - and each of those rows is distinct!
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    But we can select distinct values of a specific field so can we get distinct values of a concated field?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    CREATE TABLE MyTable99 (
        field0 int identity(1,1)
      , field1 int
      )
    
    INSERT INTO MyTable99(field1) VALUES(1)
    INSERT INTO MyTable99(field1) VALUES(1)
    INSERT INTO MyTable99(field1) VALUES(1)
    INSERT INTO MyTable99(field1) VALUES(2)
    INSERT INTO MyTable99(field1) VALUES(2)
    
    SELECT * FROM MyTable99
    
    SELECT DISTINCT field0, field1 FROM MyTable99
    
    SELECT DISTINCT field1 FROM MyTable99
    
    DROP TABLE MyTable99
    Yeah, I know... This is SQL Server syntax but you should be able to modify this (if needs) to run on mySQL.

    Hopefully you'll get the idea
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I think I'm gonna try to do it using nested qry.

    Thanks for the hints.

    Regards

    O2daO

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem is that you want some aggregate data (IP and date) and some detail data (more_columns) in one query. MySQL can't do that.

    Do you want the largest or the smallest value of "more_columns"? That could be gotten via grouping.

    Can you post two samples for us so that we can make a better guess at what you really want? One sample should show at least three whole rows of table data, one IP with only one date, one IP with two or more of the same date, and one IP with three or more of the same date. Using the sample data that you post, please create exactly what you'd like for output. Given these two samples, we can try to figure out what you really want because the example will help us more than the description can.

    -PatP

  7. #7
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I found the easiest way to do it I think.

    Here's the query I wrote to do it:
    Code:
    $qry = "SELECT LEFT(upd,8) AS date, COUNT(ip) AS hits, COUNT(DISTINCT ip) AS uniques, 'Over All' AS organized";
    $qry .= " FROM sess WHERE ((LEFT(upd,8)>='".$dtFrom."')AND(LEFT(upd,8)<='".$dtTo."')) GROUP BY LEFT(upd,8) ORDER BY LEFT(upd,8)";

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is LEFT(upd,8)? surely that's not a VARCHAR date column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Yup this is exactly what it is.

    I prefer to store dates as strings because of the differents formats from a languages to another.
    I always use one of these formats:
    yyyymmdd
    yyyymmddhhMMss

    This way I can easily sort it.

    2007/01/30 > 2006/02/28


    In this case I used yyyymmddhhMMss
    so LEFT(upd, 8) = yyyymmdd

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the horror... the horror...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Why the horror ?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dates stored as varchar <shudder>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Yes, I started doing that the day I tried to install SQL Server 2005 ent FR.
    The engine messed up all the dates in my db.

    But varchar vs dates what's the difference?
    What is the advantage to store it as dates instead of char ?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ortho
    But varchar vs dates what's the difference?
    what is the difference between a horse and a 747? both of them can take you to the wrong place

    what is the advantage of storing dates as DATEs? well, for one thing, you are guaranteed that the values will actually be dates, which is not something the database is prepared to ensure on your behalf if you use VARCHARs

    and for another, you cannot use date functions on strings
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    hum well for the date functions I agree.

    but I'm always validating my data before to write in a db so yes I can be sure my "varchardates" will be dates.

    but are dates lighter than varchars (assuming they have the same lenght)?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

Posting Permissions

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