Distinct problem - 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

SQL Server Development (2000)

Distinct problem

Author

Topic

RabBell
Starting Member

8 Posts

Posted - 2004-06-03 : 07:15:30

I know this is something stupid but it's driving me NUTS

I have an Access database which has a table called Visitors with three fields (Counter, Login, DateAdded).

I want to display all the distinct logins along with their counter number and in order of their counter number, so I was thinking a statement like this

SELECT Counter, DISTINCT Login FROM Visitors ORDER BY Counter
But I get an error message. This works though

SELECT DISTINCT Login, Counter FROM Visitors ORDER BY Counter

But then Counter is included in the DISTINCT part of the query and because Counter is an autonumber it returns every field.

Any help would be appreciated.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-03 : 07:20:01

If Counter is an autonumber then which one for the login do you want to see?
The latest one?, The earliest one?

Im not sure about the access syntax but the sql server syntax for latest counter would be something like this:

select max(counter), login
from visitors
group by login
order by max(counter)

for the earliest one change max to min.

Duane.

RabBell
Starting Member

8 Posts

Posted - 2004-06-03 : 07:35:13

No you see there was a competition launched with my companys new intranet and this table was added to track visitors to the site. Every 100th user gets a prize and we've had over 3000 visitors so far. To be honest we're running out of prizes.

So it's been requested that multiple logins be removed from the table, this would reduce it to 800.
So what I need is the Distinct Logins of the users along with the counters.

Oh wait, I just realised I need a count...not necessarily the Counter field. So I need a query that will trim 3000 to 800 by making the logins DISTINCT, order them by their Counter field and count the 800 for instance

login counter DateAdded
ZZZ15 1 2/6/04
DDD15 2 2/6/04
DFF15 3 2/6/04
DDD15 4 2/6/04
AAB50 5 2/6/04

and the query would return

login counter DateAdded newcountfield
ZZZ15 1 2/6/04 1
DDD15 2 2/6/04 2
DFF15 3 2/6/04 3
AAB50 5 2/6/04 4

Any ideas?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-03 : 07:42:18

select Login, count(*) from Visitors
group by Login
Having count(*) > 1

That will give you all the duplicates...

You could do,

Select Login, count(*), min(DateAdded) from Visitors
group by Login
Having count(*) > 1

to get the date their first login was added...

You could output these into a #temp table and then truncate and re-import them based on the date they were added to the table...

Select Login, min(DateAdded) DateAdded into #tmpAdded from Visitors
group by Login
order by min(DateAdded)

truncate table Visitors

insert into Visitors
select Login, DateAdded from #tmpAdded

Type of thing...

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-03 : 07:49:53

you'll need to create a new table with columns login, counter as int, dateadded, newcountfield as autonumber

then
run a statement like this

insert into newtable
select login, min(counter), min(dateadded)
from oldtable
group by login
order by min(counter)

Duane.

RabBell
Starting Member

8 Posts

Posted - 2004-06-03 : 07:57:10

These scripts are handy, I'll be using these no worries. Thanks.

Unfortunatlety they don't do what I was looking for.

Forget the dateadded field.
The script has to take the contents of the visitors table, remove the duplicates and order the results by the counter field.

Duane: I got an error message from this script

INSERT into NewTable
select login, min(counter), min(dateadded)
from Visitors
group by login
order by min(counter)

"No destination field name in INSERT INTO statement (min(counter))"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-03 : 08:08:38

Have you tried these scripts?
I'll bet the results of these scripts will be exactly what you are looking for!

Duane.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-03 : 08:14:49

yeah, you need to name any agregates, so:-

min(counter), min(dateadded)

needs to be:-

min(counter) counter, min(dateadded) dateadded

RabBell
Starting Member

8 Posts

Posted - 2004-06-03 : 08:15:21

What am I doing wrong, this gave me an error

INSERT into NewTable
select login, min(counter) 'counter', min(dateadded) 'dateadded'
from Visitors
group by login
order by min(counter)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-03 : 08:33:24

get rid of the single quotes...

RabBell
Starting Member

8 Posts

Posted - 2004-06-03 : 08:33:32

Gives me a different error message
OK, I think I've explained the problem badly.
Let me try again

Visitors table looks like this

Counter Login
------- ---------------------
1 --- james1
2 --- james2
5 --- james1
6 --- james2
10 --- james3

Need the query to do this

Counter(Unshown) Login NewCount
------- --------- ------------
1 --- james1 --- 1
2 --- james2 --- 2
10 --- james3 --- 3

with record 5+6 being ignored/removed

Any ideas?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-03 : 08:38:43

What is the error you are getting?? The SQL should work...

RabBell
Starting Member

8 Posts

Posted - 2004-06-03 : 08:55:44

Syntax error (missing operator) in query expression 'min(counter) counter'.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-03 : 09:09:55

Try it with the as keyword... What version of SQL are you using???

INSERT into NewTable
select login, min(counter) as counter, min(dateadded) as dateadded
from Visitors
group by login
order by min(counter)

RabBell
Starting Member

8 Posts

Posted - 2004-06-03 : 09:49:20

Thanks RickD, your script worked perfectly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources