SELECT statement with WHERE DISTINCT

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 2008 Forums

Transact-SQL (2008)

SELECT statement with WHERE DISTINCT

Author

Topic

csleech
Starting Member

5 Posts

Posted - 2010-03-03 : 14:35:49

Hello all,

First let me state I am new to SQL statements and I am trying my best to learn how to do what I need. I have a table that contains the following information in SQL (2008).

spid, login_tim, hostname, username, DatabaseName and ProgramType

What I need to do is return the following.

hostname as 'Host Name', username as 'User Name', ProgramType as 'Client Type' and login_time as 'Login Time'.

Now, I need to return these where hostname is DISTINCT. I have searched all over for this and I have found something similar to the following.

SELECT [hostname] as 'Host Name', [username] as 'User Name', [ProgramType] as 'Client Type', [login_time] as 'Login Time'
FROM [ConisioMasterDb].[dbo].[LoggedInUsers]
WHERE [hostname] IN (SELECT DISTINCT [hostname] FROM [ConisioMasterDb].[dbo].[LoggedInUsers])
ORDER By [hostname]

The problem is that it doesn't return DISTINCT only. I narrowed down the query to this.

SELECT hostname as 'Host Name'
FROM dbo.LoggedInUsers
WHERE hostname IN
(
SELECT DISTINCT hostname
FROM dbo.LoggedInUsers
)

And, as you guessed (since I am posting) it doesn't work either. I am not sure as to why this isn't working since everything I find on the internet says this is how it should work.

Any help is greatly appreciated.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-03 : 14:41:20

To get the distinct hostanames alone..you wud do this..

SELECT DISTINCT hostname as [Host Name']
FROM dbo.LoggedInUsers

But based on your explanation, it appears to can have more than one row for the same hostname. In that case, which value from the other fields do you need?
It would be easier if you provide some sample data and expected output.

csleech
Starting Member

5 Posts

Posted - 2010-03-03 : 14:52:18

Here is the results from a SELECT * FROM query with the options I want show in black.

[URL=http://img94.imageshack.us/i/11348575.png/]

[/URL]

Basically it should return just two values for hostname (since there are only two distinct). The only difference would be the headers. They would show as the information in the original post.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-03 : 14:54:00

I'm sorry..I can't access the image since the URL is blocked here.

csleech
Starting Member

5 Posts

Posted - 2010-03-03 : 14:58:42

http://2.bp.blogspot.com/_sRcEUfHjol4/S46_WvBrpRI/AAAAAAAAACU/yBmOKJ8vEHc/s1600-h/Untitled.png

Can you get to it from this?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-03 : 15:03:36

quote:
Originally posted by csleech

http://2.bp.blogspot.com/_sRcEUfHjol4/S46_WvBrpRI/AAAAAAAAACU/yBmOKJ8vEHc/s1600-h/Untitled.png

Can you get to it from this?

Nope.

..None of the content sharing sites work fomr our network...
how about you explain by just typing the sample data here. Atleast whatever little data with which u can explain your problem.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-03 : 15:22:27

Not sure of the requriements, so just a quess:

SELECT *
FROM dbo.LoggedInUsers
INNER JOIN
	(
		SELECT Hostname, MAX(login_time) AS login_time
		FROM dbo.LoggedInUsers
		GROUP BY Hostname
	) AS T
	ON LoggedInUsers.HostName = T.Hostname
	AND LoggedInUsers.login_time = T.login_time

csleech
Starting Member

5 Posts

Posted - 2010-03-03 : 15:51:36

Here is the basic table information.

spid login_time hostname username DatabaseName ProgramType
58 2010-03-03 13:47:52.620 MTMFILESHARE admin mtmtesting 0
65 2010-03-03 09:26:13.837 TODD-M90 Todd.Troutt PDMWE_ModernTechMechanical 0
63 2010-03-03 08:46:11.100 TODD-M90 Todd.Troutt PDMWE_ModernTechMechanical 0
68 2010-03-03 14:09:21.647 TODD-M90 Todd.Troutt PDMWE_ModernTechMechanical 0
59 2010-03-03 13:47:53.410 MTMFILESHARE admin mtmtesting 0
64 2010-03-03 13:55:21.913 TODD-M90 Todd.Troutt PDMWE_ModernTechMechanical 0

Here is what I want.

Host Name User Name Client Type Login Time
MTMFILESHARE admin 0 2010-03-03 13:47:52.620
TODD-M90 Todd.Troutt 0 2010-03-03 09:26:13.837

I hope this makes sense.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-03 : 15:59:04

This? It will give you the record with the max value of login time for each hostname however.

select hostname as [Host Name]
,username  as [User Name]
,ProgramType as [Client Type]
,max(login_time) as [Login Time]
from dbo.LoggedInUsers
group by hostname,username,ProgramType

csleech
Starting Member

5 Posts

Posted - 2010-03-03 : 16:10:38

Thats it!!!! Thank you so much!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-03 : 16:36:38

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources