Distinct Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Distinct Query Help

Author

Topic

stk
Starting Member

7 Posts

Posted - 2005-06-07 : 11:32:54

This is my first post. I have a simple table with the following structure:

timestamp(primary) int(15)
totIP varchar(40)
ip (index) varchar(40)
file (index) varchar(100)

Sample population:


      timestamp  totIP         ip         file
      1118157449 205.206.9.123 205.206.9 /2005/06/01/offsite_links_open_new_windows 
      1118157447 205.206.9.123 205.206.9 /2005/06/01/offsite_links_open_new_windows 
      1118157446 205.206.9.4   205.206.9 /index.php 
      1118157445 205.206.9.123 205.206.9 /2005/06/01/offsite_links_open_new_windows 
      1118157444 205.206.9.4   205.206.9 /index.php 
      1118157442 205.206.9.4   205.206.9 /index.php 
      1118157437 205.206.9.141 205.206.9 /2005/05/29/sponging_it_up 
      1118157435 205.206.9.141 205.206.9 /2005/05/29/sponging_it_up 
      1118157433 205.206.9.141 205.206.9 /2005/05/29/sponging_it_up 
      1118157277 207.46.98.88  207.46.98 /2005/03/24/gene_altered_foods 
      1118157275 207.46.98.88  207.46.98 /2005/03/24/gene_altered_foods 
      1118157273 207.46.98.88  207.46.98 /2005/03/24/gene_altered_foods

From this example, I would like to return the full IP address and filename from each unique 3-character IP address, having the maximum timestamp value (2 rows):

207.46.98.88 /2005/03/24/gene_altered_foods
205.206.9.123 /2005/06/01/offsite_links_open_new_windows

I'm new to SQL, have been trying various SELECT DISTINCT statements, but getting only errors or the incorrect results. I'm assuming that such a query is possible. Any help would be greatly appreciated.

-stk

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-07 : 11:35:46

How about


Select A.*
From <yourTable> A
Inner Join (select ip, timestamp=max(timestamp) from <yourTable> Group By ip) B
On A.ip = B.ip
and A.timeStamp = B.timeStamp

PS... didn't test

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

stk
Starting Member

7 Posts

Posted - 2005-06-07 : 11:46:30

Oops ... tablename = "evo_online"

Nope ... tried

quote:
SELECT A . *
FROM evo_online A
INNER JOIN (
SELECT ip, timestamp = max( timestamp )
FROM evo_online
GROUP BY ip
)B ON A.ip = B.ip
AND A.timestamp = B.timestamp

but received an MySQL error:

quote:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select ip, timestamp=max(timestamp) from evo_online Group By ip

Thanks for your quick reply.

Are A and B copies of the table?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-07 : 11:54:19

quote:
Originally posted by stk

Oops ... tablename = "evo_online"

Nope ... tried
quote:
SELECT A . *
FROM evo_online A
INNER JOIN (
SELECT ip, timestamp = max( timestamp )
FROM evo_online
GROUP BY ip
)B ON A.ip = B.ip
AND A.timestamp = B.timestamp
but received an MySQL error:
quote:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select ip, timestamp=max(timestamp) from evo_online Group By ip

Thanks for your quick reply.

Are A and B copies of the table?

you should specify that it is MySQL...

try


Create Table #myTable (ip varchar(20), timestamp int)
insert into #myTable SELECT ip, timestamp=max(timestamp) From evo_online GROUP BY ip


SELECT evo_online. * 
FROM evo_online
INNER JOIN #myTable
ON evo_online.ip = #myTable.ip
AND evo_online.timestamp = #myTable.timestamp

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-07 : 11:55:11

P.S. A & B are aliases, and I was joining a subquery of a table to the table itself.

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

stk
Starting Member

7 Posts

Posted - 2005-06-07 : 12:02:55

Sorry, didn't know to specify MySQL. What's the diff between MySql and Sql? Is one oracle and the other some other vendor?

I must create a table in order to make the query?

I'll give it a try.

stk
Starting Member

7 Posts

Posted - 2005-06-07 : 12:07:43

Hmmm. Getting a MySQL error trying to create the table.

quote:
CREATE TABLE #myTable (ip varchar(20), timestamp int)
INSERT INTO #myTable SELECT ip, timestamp=max(timestamp) From evo_online GROUP BY ip

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into #myTable SELECT ip, timestamp=max(timestamp) From e

Part of the problem with creating a table is that the data are dynamically created in evo_online and it's constantly changing. I suppose I should create a static version from which to test from.

stk
Starting Member

7 Posts

Posted - 2005-06-07 : 12:19:22

If I run a

quote:
SELECT ip, timestamp = max( timestamp )
FROM evo_online
GROUP BY ip

Against the evo_online DB, it returns:


ip             timestamp=max(timestamp)
205.206.9      0
207.46.98      0

Is this expected, or are you expecting to see the actual UNIX timestamp value?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-07 : 12:21:19

This is a MS SQL Server forum. MySql is built by a different vendor (as is oracle) While these all have SQL query capability, the functionality and syntax is slightly different for each.

I do not know MySql syntax, so you will have to look that up or visit a MySql forum, or maybe someone here will be able to help you out further.

For the error you are receiving it is not a data issue, it is a syntax issue.

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

stk
Starting Member

7 Posts

Posted - 2005-06-07 : 12:27:10

Hmmm. OKAY ... I only saw the "SQL Team.com" thing when I signed up, but I see the "Microsoft SQL Server articles ..." box in the upper right, now that you mention it.

Sorry ... I'll see if I can find a more appropos forum.

Thanks for your attempt to help a poor SQL neophyte!

-stk

stk
Starting Member

7 Posts

Posted - 2005-06-07 : 12:56:46

Bummer ... I'm REALLY close!

The query

SELECT timestamp = max( timestamp ) , timestamp, ip, totIP, 
FILE 
FROM evo_online
GROUP BY ip
ORDER BY timestamp DESC

Yields pretty much what I want. The only problem that I see, is that the timestamp=max(timestamp) seems to return a 0/1 (true/false), meaning that it doesn't always return the maximum timestamp value for a unique IP.

(The folks on the mySql forum aren't as prompt as you guys ... darn. Makes me wish it were an MS SQL query)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources