SQL SELECT DISTINCT returns duplicates...why??? 3

[POSAPAY]

Hi,

I'm puzzled..perhaps someone can shine a little light on my stupidity...

I'm trying to retrieve a log from a table,
and only get distinct IPAddress-es, but no restriction on other fields.

this is my SQL string sofar.. and I'm not sure what I'm wrong on.

SELECT DISTINCT IPAddress, LogID, Domain, Browser, WebAddress, PageLoaded, Parameters, DateCreated FROM tblLogs ORDER BY DateCreated DESC

It lists everything, just as if I didn't have the DISTINCT in there.
Also, I'd like to restrict it to TOP 100.. and I just got errors with that for some reason.

thanks,
-Peter

 

[Gatchaman]

i'm no SQL expert, so I can't really explain it too well. But, when you do a SELECT DISTINCT in the way that you are doing it, since you are including all the other fields you are asking for ALL records where "IPAddress, LogID, Domain, Browser, WebAddress, PageLoaded, Parameters, DateCreated" are all unique, and since I guess there are no two records with all the data from all the fields you are looking at the same, it looks like the DISTINCT isn't working.

To get all unique IPAddress then you'd have to do a SELECT DISTINCT just on that, ie.

SELECT DISTINCT IPAddress FROM tblLogs

I'm not sure if you can do a sort on DateCreated in that, but if you can then you could just loop through the recordset from that and get all the info using "WHERE IPAddress = '" & RS.Fields("IPAddress&quot & "'"

This post is best viewed at 1024 x 768

 

[JoJoH]

Hi Peter,

For example, if you have 3 records in your database table:

IP Address Web Address Browser
Record 1: 123.05.325 site1.com Browser=IE
Record 2: 123.05.325 site1.com Browser=IE
Record 3: 123.05.325 site1.com Browser=IE

Then if you do a select DISTINCT IP Address, Web Address, Browser from tblLogs, natually it will return this result:

IP Address Web Address Browser
123.05.325 site1.com Browser=IE


Now say you have these 3 records in your database table:

IP Address Web Address Browser
Record 1: 123.05.325 site1.com IE
Record 2: 123.05.325 site1.com NS
Record 3: 123.05.325 site1.com IE

Now the difference between these 3 and the previous 3 records is in record 2, the person is using NS browser instead of IE. Now if you do the exact same SELECT DISTINCT statement above the result will be this

IP Address Web Address Browser
123.05.325 site1.com Browser=IE
123.05.325 site1.com Browser=NS

See it returned 2 records even thought the IP and the web address is the same. So if you want to get a unique IP Address in this example, then you will have to get rid of the "Browser" field from your SELECT DISTICT statement.

So you SQL should look something like this

SELECT DISTINCT IP Address, Web Address from tblLogs

Then it will return the distinct IP address.

Hope this helps!

JoJoH

http://www.jadeboutique.com

 

[POSAPAY]

JoJoH,

you're right, and it would work,
except I want to display LogID(which is different for every record) and DateCreated(which is alsi different for each record)

I guess I might need to nest two SQL commands.. and the outerone loading distinct IPAddress-es and the innerone looking all info up ordering it by datecreated.. and only displaying the one most recent.

Unless there is a simpler way... Is there a cimpler way?

-Peter

 

[vbkris]

hows about this:
SELECT DISTINCT(IPAddress), LogID, Domain, Browser, WebAddress, PageLoaded, Parameters, DateCreated FROM tblLogs ORDER BY DateCreated DESC


Known is handfull, Unknown is worldfull

 

[vbkris]

oops sorry my query is wrong. didnt get ur situation... what Gatchaman said is correct (u have to use 2 sql)...

Known is handfull, Unknown is worldfull

 

[Tarwn]

Nope, you can do it in one. Order by the IP Address as well.
If you want it sorted first by IP and then inside thast by date:

...ORDER BY IPAddress, DateCreated

or by date than IP's on that Date
...ORDER BY DateCreated, IPAddress

I assume the reason you wanted ther IP seperate from the records is for display purposes. With the double sort you willstill have that ability, you jut need a little extra code. I'll use the first one as the example:

'assume we executed that statement and brought the results back into a recordset named rs

Dim lastIP
Response.Write &quot;<table>&quot;
Do Until rs.EOF
   If lastIP <> rs(&quot;IPAddress&quot;) Then
      'start a new block for a new IP
      Response.Write &quot;<tr><th colspan=&quot;&quot;7&quot;&quot;>&quot; & rs(&quot;IPAddress&quot;) & &quot;</th></tr>&quot;
      lastIP = rs(&quot;IPAddress&quot;)
   End If

   'write out a record
   Response.Write &quot;<tr><td>&quot; & rs(&quot;LogID&quot;) & &quot;</td><td>&quot; &  rs(&quot;Domain&quot;) & &quot;</td><td>&quot; &  rs(&quot;Browser&quot;) & &quot;</td><td>&quot; &  rs(&quot;WebAddress&quot;) & &quot;</td><td>&quot; &  rs(&quot;PageLoaded&quot;) & &quot;</td><td>&quot; &  rs(&quot;Parameters&quot;) & &quot;</td><td>&quot; &  rs(&quot;DateCreated&quot;) & &quot;</td></tr>&quot;

   rs.MoveNext
Loop
Response.Write &quot;</table>&quot;

That will loop through your entire recordset outputting the entries in groups by IPAddress. It should be functional, so you could give it a try after altering your SQL statement.

One thing you will need to think about. What would you want a top 100 to return? The last 100 entries by date? If this is the case, then you will need a slightly more complex SQL statement for the first one, but the second one should work just fune with just the top 100 statement in it. Also, you do not need the distinct.

-Tarwn

[sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Do you know how hot your computer is running at home? I do

http://www.tiernok.com/hscripts/systemp.asp

 

[JoJoH]

Hi Peter,

Remember what I told you before about the "web page format"?(That was a bad name for it, I was in a hurry yesterday) You could have it in the same page too! You could have a page that is seperated into 2 parts, top and bottom. The bottom part will have all the IP addresses you want and the date created, then when you click on any one of them, the details of that IP will show up in the top part. You should definitely try out what Tarwn said too, it looks extremely logical to me, sweet and simple! If what he said does works than by all means go for that instead of the web pg format I've just told you, cuz the web pg format has a little more coding and is more complicated (more time too) but it is a nice alternative to accomplish what you want!

Good Luck!

JoJoH

http://www.jadeboutique.com

 

[POSAPAY]

Tarwn,

that would work pretty good, but if I see it right, it would still get from the SQL server every record, and display every record.

What I'd like to achive, is to only display one record per IP address, with all the fields next to it in separate columns, including the DateCreated, which should show the last time that user visited.

Basicly I'd like to achieve in one SQL statement/request the following:

SELECT DISCTINCT IPAddress from tblLogs
...
Do while not FirstRecordset.EOF
SELECT * From tblLogs WHERE IPAddress LIKE FirstRecordset.Fields("IPAddress&quot ORDER BY DateCreated Desc
...
<TR><TD>field1</td><td>field2</td><td>....</td></tr>
loop

If I'm right, this would do it..
but I'm trying to find a little simpler solution, with only one request going to the SQL server.

Saving bandwidth..and time.

-Peter

 

[onpnt]

how about incorpurating a TOP clause in the statement

____________________________________________________
[sub]The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924[/sub]

http://www.onpntwebdesigns.com

 

[POSAPAY]

onpnt,

You are absolutely right! I meant to put that in.. that par isn't the challanging part....its the distinct with all different LogID-s and DateCreated-s fields.

Thanks for pointing it out!

-Peter

 

[Tarwn]

I'm not sure I understand...
Your example selects all the IP addresses (distinctly)
Then it goes back and selects all of the records for each individual ip address and displays them...
Thats what mine is doing...?

I mean, are you looking for output like this:

IP Address	LogId	Date		etc
111.111.111.111	1	2/2/2003
		4	2/4/2003
		5	2/6/2003
		7	2/12/2003
111.111.111.22	2	2/2/2003
		3	2/3/2003
123.45.67.89	6	2/10/2003
		8	2/15/2003

Or just output for the last time each IPAddress was logged, like so:

IP Address	LogId	Date		etc
111.111.111.111	7	2/12/2003
111.111.111.22	3	2/3/2003
123.45.67.89	8	2/15/2003

The first one is what your code above would produce and is very similar to my original post, all you would have to do is change the formatting of the table output some, the sql wouldn't need to change at all.

I'm not sure I understand what kind of output your after...



[sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Do you know how hot your computer is running at home? I do

http://www.tiernok.com/hscripts/systemp.asp

 

[DiegoErnesto]

Hello Peter;

Try This:

SELECT t1.IPAddress, t1.LogID, t1.Domain, t1.Browser, t1.WebAddress, t1.PageLoaded, t1.Parameters, t1.DateCreated
FROM tblLogs t1
(SELECT DISTINCT TOP 100 IPAddress,
max(DateCreated) as DateCreated
FROM tblLogs
GROUP BY IPAddress
) as t2
WHERE t1.IPAddress = t2.IPAddress and
t1.DateCreated = t2.DateCreated
ORDER BY DateCreated DESC

This work; the problem is if you IPAddress and DateCreated is not unique, then include other variable to subquery and where.

Diego.