Select Distinct On Concated Fields

ortho · #1 (**permalink**) 09-20-07, 11:04

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

gvee · #2 (**permalink**) 09-20-07, 11:12

A common misconception...
DISTINCT is applied to all selected rows - and each of those rows is distinct!

ortho · #3 (**permalink**) 09-20-07, 11:35

But we can select distinct values of a specific field so can we get distinct values of a concated field?

gvee · #4 (**permalink**) 09-20-07, 11:47

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

ortho · #5 (**permalink**) 09-20-07, 15:45

I think I'm gonna try to do it using nested qry.

Thanks for the hints.

Regards

O2daO

Pat Phelan · #6 (**permalink**) 09-20-07, 19:12

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

ortho · #7 (**permalink**) 09-21-07, 13:54

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)";

r937 · #8 (**permalink**) 09-21-07, 13:57

what is LEFT(upd,8)? surely that's not a VARCHAR date column?

ortho · #9 (**permalink**) 09-21-07, 14:25

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

r937 · #10 (**permalink**) 09-21-07, 14:35

the horror... the horror...

ortho · #11 (**permalink**) 09-21-07, 14:38

Why the horror ?

r937 · #12 (**permalink**) 09-21-07, 14:42

dates stored as varchar <shudder>

ortho · #13 (**permalink**) 09-21-07, 14:46

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 ?

r937 · #14 (**permalink**) 09-21-07, 14:59

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

ortho · #15 (**permalink**) 09-21-07, 15:04

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)?

Internet Services

Web Development

Digital Marketing

Consumer Tech