distinct question - 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 question

Author

Topic

settinUpShop
Starting Member

28 Posts

Posted - 2003-11-17 : 22:58:37

When using distinct in a select statement like so...

SELECT DISTINCT au_id
FROM titleauthor

...you get the desired results of only unique au_id's returned. But why is it that if you try to return several other columns then it no longer works?

Here's the actual query I'm trying to accomplish:

SELECT DISTINCT Email_Address,UserRole_Name,Access_Rights
FROM (
SELECT Email_Address,UserRole_Name,Access_Rights
FROM UserInfo
JOIN UserInfo_UserRole
ON UserInfo.UserInfoID = UserInfo_UserRole.UserInfoID
JOIN UserRole
ON UserRole.UserRoleID = UserInfo_UserRole.UserRoleID
WHERE UserRole.UserRole_Name IN ('super','super publisher','optimizer')
UNION
SELECT Email_Address,UserRole_Name,Access_Rights
FROM UserInfo
JOIN UserInfo_UserRole
ON UserInfo.UserInfoID = UserInfo_UserRole.UserInfoID
JOIN UserRole
ON UserRole.UserRoleID = UserInfo_UserRole.UserRoleID
WHERE UserRole.UserRole_Name = 'publisher' AND UserInfo.Access_Rights LIKE '%' + @PageSectionID + '%'
) Subquery1

Do I have to drop the UserRole_Name,Access_Rights columns from the outer select?

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-17 : 23:09:32

It may not be working the way you expect, but it is probably working. BOL's definition of DISTINCT

quote:
The DISTINCT keyword eliminates duplicate rows from the results of a SELECT statement. If DISTINCT is not specified, all rows are returned, including duplicates. For example, if you select all the author IDs in titleauthor without DISTINCT, the following rows are returned (with some duplicate listings):

Without a description of the returned recordset of the 2nd query, it's hard to figure what might be going on.

Sam

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-17 : 23:54:33

DISTINCT works on rows not on columns. Your statement will return unique combinations of the four columns in your list. Think about it yourself, if you have a resultset like this:


au_id	title_id
10	255
10	310
15	196
17	130

SELECT DISTINCT au_id, title_id FROM Table1

And you try to do a distinct on the au_id column, but include the title_id column as well, which row should the query return for au_id: 10? If it returns both au_id, is no longer distinct. In such a case, you will have to have to perform some sort of aggregatation on the columns that are causing the duplication. Some ideas:


SELECT au_id, COUNT(title_id) FROM Table1
SELECT au_id, MIN(title_id) FROM Table1

You could also think of returning a comma separated list of title_id values in a single column. See http://www.sqlteam.com/SearchResults.asp?SearchTerms=CSV for ideas.

Owais

Owais

Where there's a will, I want to be in it.

settinUpShop
Starting Member

28 Posts

Posted - 2003-11-18 : 09:18:26

Great, thank you for the suggestions and explaination. I discovered I don't need the other two columns so the query I was using will work, just returning the Email_Address column. But now I understand that distinct works across rows, so the results I was getting now make sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources