SQL Distinct function din't work

Question

Here i have a table called tblemployee which consists id,name and salary column.The name and salary column consists five rows, name column consists 3 different name (i.e each name in name column does not match with another name) while the salary column consists the same integer value (i.e 40,000 in each row of salary column).

Table tblemployee structure

name|salary
-----------
max |40000
rob |40000
jon |40000

Now what i want is that, i want all the names from name column but only one salary value from salary column as shown below:

name|salary
-----------
max |40000
rob |
jon |

Sql Server query i have tried which didn't give the expected output

select DISTINCT salary,name from tblabca

score 3 · Accepted Answer · answered Dec 26 '16 at 17:10

Declare @tblemployee table (name varchar(25),salary int)
Insert Into @tblemployee values
('max',40000),
('rob',40000),
('jon',40000),
('joseph',25000),
('mary',25000)

Select Name
      ,Salary = case when RN=1 then cast(Salary as varchar(25)) else '' end
 From (
Select *
      ,RN = Row_Number() over (Partition By Salary Order By Name)
      ,DR = Dense_Rank() over (Order By Salary)
 From  @tblemployee
) A
Order by DR Desc,RN

Returns

Name    Salary
jon     40000
max 
rob 
joseph  25000
mary

@sudipchand Happy it helped. Well worth your time to get comfortable with the window functions. They can be INVALUABLE — John Cappelletti, Dec 26 '16 at 17:32

score 0 · Answer 2 · edited May 23 '17 at 12:08

0

"GROUP BY" and group_concat would suit your case. Please try like this

select salary, group_concat(name) from tblabca group by salary;

Reference: GROUP BY, GROUP_CONCAT

edited May 23 '17 at 12:08

Community

1
1

answered Dec 26 '16 at 16:49

Tom Taylor

3,344
2
38
63

If you group by salary you won't get all the names. – nicomp Dec 26 '16 at 16:53
@rajasuba : thanks for help but it will give name is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause error – CrossWords Dec 26 '16 at 16:58
No @nicomp It would give only the first name (ie; the first row which matches your criteria) in your table. – Tom Taylor Dec 26 '16 at 17:01
Group by concats "concatenates the list of selected row into a single row" and gives us the result. – Tom Taylor Dec 26 '16 at 17:02
Yeah it will..@ShakeerMirza – Tom Taylor Dec 26 '16 at 17:07
Did MS slip group concat into sql server without telling anyone about it? – David Söderlund Dec 26 '16 at 17:15
I dont think so. Group_Concat is a mysql function. Recheck it once @RajasubaSubramanian – Shakeer Mirza Dec 26 '16 at 17:17
I'm not sure about sql server @ShakeerMirza. Have to try in it.. I haven't tried it yet.. – Tom Taylor Dec 26 '16 at 17:30

Shakeer Mirza · Answer 3 · 2016-12-26T17:20:17.507

You will never get the result as you stated. Because DISTINCT operator works on a SET. not on individual column. In a relational database you will only work with sets.

So the combination of Salary and Name will be treated as Distinct.

But if you want you can get names in comma concatenated list like below

 SELECT SALARY 
 , STUFF ((SELECT ','+NAME From TableA T2 
 WHERE T1.SALARY = T2.SALARY FOR XML PATH('') 
 ),1,1,'') FROM TABLEA T1

score 0 · Answer 4 · answered Dec 26 '16 at 17:20

As others already stated, you are definately not looking for DISTINCT operator.

The distinct operator will work upon the entire result set, meaning you'll get result rows that are unique (column by column).

Although with some rework you might end up with the result you want, do you really want the result in such a not uniform way? I mean, getting a list of names on the name column and only one salary on the salary column do not look like a nice result set to work with.

Maby you should work on your code to account for the change you want to make in the query.

Daniel Carbajal López · Answer 5 · 2016-12-26T18:49:10.370

declare @tblemployee Table(
    id int identity(1,1) primary key not null,
    name nvarchar(MAX) not null,
    salary int not null
);

declare @Result Table(
    name nvarchar(MAX) not null,
    salaryString nvarchar(MAX)
);

insert into @tblemployee(name,salary) values ('joseph' ,25000);
insert into @tblemployee(name,salary) values ('mary' ,25000);
insert into @tblemployee(name,salary) values ('Max' ,40000);
insert into @tblemployee(name,salary) values ('rob' ,40000);
insert into @tblemployee(name,salary) values ('jon' ,40000);

declare @LastSalary int = 0;
declare @name nvarchar(MAX);
declare @salary int;



DECLARE iterator CURSOR LOCAL FAST_FORWARD FOR
    SELECT  name,
            salary
    FROM    @tblemployee
    Order by salary desc
OPEN iterator
FETCH NEXT FROM iterator INTO @name,@salary
WHILE @@FETCH_STATUS = 0 
    BEGIN
        IF (@salary!=@LastSalary)
        BEGIN
            SET @LastSalary = @salary
            insert into @Result(name,salaryString)  
            values(@name,@salary+'');
        END
        ELSE
        BEGIN 
            insert into @Result(name,salaryString)  
            values(@name,'');
        END
        FETCH NEXT FROM iterator INTO @name,@salary
    END

Select * from @Result

SQL Distinct function din't work

5 Answers5