select distinct - 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)

select distinct

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-16 : 02:48:11

I have an online users table that has each online session in the table. This table stores, OnlineName, sex, age, stateprovID.

When a user logs on twice and has 2 sessions there are 2 spots in the table with the same nameonline. In addition about 50% of the rows do not have a name in nameOnline, however this value is not null.

How can I bring back every column without bringing back duplicate "nameOnlines" ?? Ive been rackin my brain for hours and cant seem to find any good articles.... thanks

Nazim
A custom title

1408 Posts

Posted - 2002-01-16 : 03:09:59

will this not do it
select distinct OnlineName, sex, age, stateprovID from tablename

----------------------------------
"True love stories don't have endings."

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-01-16 : 03:44:39

I did it like this once. My goal was to have a list of users currently logged in. I stored User ID and the Session ID in one table.

When you log in it checks to see if your User ID is in there and if it is, it lets you in without saving again.

Anyways. in global.asa you can make it on session close have an sql statement removing the row with the current session ID. This way no user can actually log in twice and if he doesnt log out, global.asa will do it for you.

This is one way of doing it.

=====================================
Why not try and do the impossible?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-16 : 03:45:50

given what I said yes it does work ... I forgot to add one part.

There is also a field name "pageView" which stores the page they are currently viewing which is always changing... when one user session with onlinename "mike" is in the table as looking at "page 1" and another session is at page "2" the distinct clause does not seperate them...... any insight?

cheers

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-16 : 05:14:45

swede

ive done it just a bit differently than that because of my auto-logon ..
same principles tho

thx

Nazim
A custom title

1408 Posts

Posted - 2002-01-16 : 07:31:40

select OnlineName, sex, age, stateprovID,min(pageviews) from tablename
group by OnlineName, sex, age, stateprovID

HTH

----------------------------------
"True love stories don't have endings."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-16 : 15:11:24

nazim It still returns rows such as these, the first 4 columns here should be identical but for some reason they are not (info is pulled from cookies) is there a way to further modify this statement to correct for this error ?

thanks..

Disco_Pimp 1 23 0 Home Page
Disco_Pimp 1 23 44 Message Forums

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-01-16 : 15:24:37

quote:

nazim It still returns rows such as these, the first 4 columns here should be identical but for some reason they are not (info is pulled from cookies) is there a way to further modify this statement to correct for this error ?

thanks..

Disco_Pimp 1 23 0 Home Page
Disco_Pimp 1 23 44 Message Forums

mike123,

Why don't you show us some of your data and then what you'd like the result to look like. I don't understand what you're looking for.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-16 : 15:52:01

807802126 *NO DATA* 0 0 0 page1
807802136 *NO DATA* 0 0 0 page1
807802137 *NO DATA* 0 0 0 page1
807802138 vancity 1 20 53 page1
807802139 *NO DATA* 0 0 0 page1
807802145 xtc 1 22 53 page1
807802155 *NO DATA* 0 0 0 page1
807801887 billy 1 19 0 page1
807802156 xtc 1 22 53 page1
807801902 skater 1 19 0 page1
807802160 *NO DATA* 0 0 0 page1
807801965 *NO DATA* 0 0 0 page1

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-16 : 16:01:21

First off heres the column names for clarity

SessionID, nameOnline, sex, age, stateProvID, pageView

Thought I'd post the data separately for easier reading. Anyways I'll clear things up. The above data is from "tblactive_users" this table stores all the active user sessions. When a user logs in it updates their session with their login info. However, occasionally a user will either a) logon twice and have 2 sessions or b) for some reason the cookie fails and their logon info is for age column or stateprovID column different.. anyways... Becuase of this I want to select everyrecord (and all columns associated)without selecting duplicate nameOnlines. If possible it would be nice to select the nameOnlines only where there is an acutal name.. note: the columns without names are not null they are empty... If this cant be done its no big deal I can just go through them in asp. Hope this is alot clearer .. thanks!!!

Nazim
A custom title

1408 Posts

Posted - 2002-01-17 : 01:20:44

Mike, When Graz asked you to provide data he also meant the result what you are looking from that data. you question is still vague.

quote:

Becuase of this I want to select everyrecord (and all columns associated)without selecting duplicate nameOnlines

select * from tablename
where len(rtrim(ltrim(columnname)))=0
should give you the records where the data is empty but not null.

quote:

If possible it would be nice to select the nameOnlines only where there is an acutal name.. note: the columns without names are not null they are empty..

If you can provide the result you are looking against the data . We can help you.

----------------------------------
"True love stories don't have endings."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-17 : 01:26:50

Sorry if I was unclear. What I would like to select is quite simple actually.

Everything except I do not want duplicate usernames.

What seems to be making it more complex for me is that for instance
if there were 2 rows with the same "ONLINENAME" I only want one of them returned, however the other columns in these 2 rows may be different. ( hope that didnt confuse things ;) )

It's a who's online status page so its gonna display everyuser online just once along with their age, sex, location. (Sometimes a user can be logged in and have 2 rows in the table with different ages, sex)

I know im rambling... Ive been working wayyyyyyyyy too long .. :D

thx

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-17 : 01:53:34

to clarify and hopefully not confuse Im playing with things a bite more here

bob 22 1 53 Home Page
bob 22 1 53 Message Forums

In the above situation I would only want 1 row returned .. but since the last column is different the distinct clause does not seperate them???

thx ;)

izaltsman
A custom title

1139 Posts

Posted - 2002-01-17 : 02:54:07

quote:

(Sometimes a user can be logged in and have 2 rows in the table with different ages, sex)

Different sex? I wonder how that happens...

So as long as you don't have any users with the same sessionID, but multiple age/sex etc attributes something like this should work:


SELECT 
	uq_users.sess
	, uq_users.nameOnline
	, info.sex
	, info.age
	, info.stateProvID
	, info.pageView
FROM 
	(SELECT nameOnline, min(sessionID) as sess
	FROM tblactive_users
	WHERE nameOnline <> ''
	GROUP BY nameOnline) as uq_users 
	INNER JOIN tblactive_users as info
		ON uq.users.sess = info.sessionID AND uq_users.nameOnline = info.nameOnline

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-17 : 21:35:29

your a good man thx!!

worked perfectly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources