Jr. DBA asked me a day ago, how to apply DISTINCT keyword to only first column of SELECT. When asked for additional information about question, he showed me following query.
SELECT Roles, FirstName, LastName
FROM UserNames
He wanted to apply DISTINCT to only Roles and not across FirstName and LastName. When he finished I realize that it is not possible and there is logical error in thinking query like that. I helped him with what he needed however, after he left I realize that answer to his original question was “NO”.
Distinct can not be applied to only few columns it is always applied to whole column. In following exam DISTINCT is applied across Roles, FirstName and LastName.
SELECT DISTINCT Roles, FirstName, LastName
FROM UserNames
Same result as above query can be achieved by using GROUP BY as well.
SELECT Roles, FirstName, LastName
FROM UserNames
GROUP BY Roles, FirstName, LastName
Before I end discussion I would like to add that DISTINCT can be used with aggregated functions and can be applied on only one field in that case. Following example demonstrates that scenario.
SELECT MAX(DISTNCT ID), Roles, FirstName, LastName
FROM UserNames, LastName
This subject is very interesting and I decided to search online if any other writers has explained this in depth. I found following good article written by Jeff Smith. He has explained this example in depth so I have skipped explaining similar example in depth.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
In the following sentence you made a mistake: “Distinct can not be applied to only few columns it is always applied to whole column.” You must have meant the “whole row”, not column. And while at it, you may want to add a punctuation sign in front of “it is…”. :)
my concept is not clear after reading your discussion
It seems to me that M$ SQL Server cannot “GROUP BY” one column of a result set, nor can it select only “DISTINCT” values of one column of the result set.
MySQL can easily do this with the following code:
“SELECT DISTINCT Roles, FirstName, LastName FROM UserNames”
h i
using stored produce i want to remove the duplicate value in the single colums
Hi,
I have a similar problem but cant fix it can you please help me, problem is:
I have a table with following columns: basketid, lookup_value, date_time.
I only want to return distinct basketid and lookup value rows and also want to include date_time row as well. but it is not distinct. any solutions please. Group by and distinct does not solve my problem.
Thank you
@Naeem
One way to do that is use GROUP BY, But there is a serious problem with GROUP BY, which I hate saying but true, You have to include all the columns in GROUP BY clause what you have mentioned in SELECT clause.
Anyways your best choice would be to use OVER PARTITION BY in your select query, this is a new feature in sql server 2005.
OVER PARTITION BY will serves your purpose. This is just like GROUP BY but here you have freedom to exclude columns that you do not want to include in GROUP BY clause.
GROUP BY is nothing but grouping or distinct (for that match of columns) in a way.
Find simple example of OVER PARTITION BY here,
http://www.sqlteam.com/article/sql-sever-2005-using-over-with-aggregate-functions
~ IM.
I’m surprised at your statement about ” logical error in thinking query like that… ”
I have a situation where I’m asked for a sample of 100 records from a 10,000 record table. The table has the following columns ( ID, PayerID, Category, Status, CreateDate )
In my Sample file I need to have a 100 records with all the columns but each record should have a DISTINCT PayerID.
Explain to me please on how would you solve this?
Thanx
Kinara
@Kinara,
Need more details.
Do you have duplicate records for the same PayerID with different combinations of ID< Category, Status and CreateDate. If there exusts multiple records for same PayerID, I dont think, you can achieve your tasks by a simple select statement.
Please provide some sample data.
Input and what kind of output you are expecting.
~ IM.