SQL distinct

still returning multiple records?

Page 1 of 1

3 Replies - 2208 Views - Last Post: 26 May 2002 - 08:54 PM Rate Topic: -----

#1 supernova333  Icon User is offline

  • D.I.C Addict

Reputation: 4
  • View blog
  • Posts: 590
  • Joined: 12-March 02

SQL distinct

Posted 24 May 2002 - 02:27 AM

I am using CF with an Access DB.  This is a table of site visitors, it has their IP and some other info. I want to make a report page with each IP listed once, but srill include info from other columns.  When I try a "select distinct" it still gives me all the records, like it is doing a distinct select on all the columns I have listed instead of just "address".  Here's what I have:

select distinct address, host, created, referer, agent, id
from counter
order by id desc

Like I said, no sql errors, it just gives me every record in the database even though there are lots of duplicates in the address column.  Any ideas?  Thanks in advance!!



Is This A Good Question/Topic? 0
  • +

Replies To: SQL distinct

#2 gneato  Icon User is offline

  • <title>Untitled Document</title>

Reputation: 0
  • View blog
  • Posts: 1,311
  • Joined: 03-September 01

Re: SQL distinct

Posted 24 May 2002 - 10:45 AM

if you have duplicates... umm.. you need to make it check for that visitor before it adds another entry...

or have a relational database... like have a visitor table with all the id numbers (mabe ip address, although many are dynamic) and log the visits in another table...

with MySQL you can use LIMIT (0,30) I believe it is to only list the first 30...  (31,60) to list the next... so on...

I'm new to sql though so you should check the syntax

my typing today makes no sense...

Was This Post Helpful? 0
  • +
  • -

#3


Reputation: 0

Re: SQL distinct

Posted 26 May 2002 - 08:52 PM

I believe you need to select distinct in parens to get distinct on that field.  Are you using .asp?  I assume so since you are pulling from an access database.  Here is some code I used for a client.

strSQL = "SELECT Distinct(strDate), intClientID FROM activities " & _
"WHERE strDate >= #" & dtStart & "# " & _
"AND strDate <= #" & dtEnd & "#;"

Was This Post Helpful? 0

#4


Reputation: 0

Re: SQL distinct

Posted 26 May 2002 - 08:54 PM

Almost forgot on the LIMIT command the second 30 would be:
LIMIT 30, 30
basically the first number is how many to skip and the second number is how many to list.  

Hoopster

Was This Post Helpful? 0

Page 1 of 1