# Developers Planet

DheerajJain February 2016

I used self join to compare but I do not know how to include a comment column to explain whose is higher than who.

Expect something like shown below:

``````empid   name     salary comments
1       raj      30000  "raj sal less then dev and sunil but greater then prashant,dilip,anil"
2       prashant 24935  "prashant sal less then raj,dev,anil,sunil but greather then dilip"
3       dev      38572
4       anil     29845
5       sunil    48201
6       dilip    12093
``````

Matt February 2016

Use the `STUFF` function within a `CASE` statement.

``````SELECT t1.empid, t1.name, t1.salary,
CASE WHEN t1.salary = (SELECT MIN(t3.salary) FROM yourtable t3)
THEN t1.name + ' has the lowest salary.'
WHEN t1.salary = (SELECT MAX(t3.salary) FROM yourtable t3)
THEN t1.name + ' has the highest salary.'
ELSE t1.name +'''s salary is less than ' + STUFF((SELECT ', ' + CAST(T2.name AS VARCHAR(MAX))
FROM yourtable T2 WHERE  T1.salary < T2.salary
FOR XML PATH('')),1,1,'') + ' but greater than ' + STUFF((SELECT ', ' + CAST(T2.name AS VARCHAR(MAX))
FROM yourtable T2 WHERE  T1.salary > T2.salary
FOR XML PATH('')),1,1,'')  + '.'
FROM yourtable t1
GROUP BY t1.empid, t1.name, t1.salary
``````

OUTPUT

``````empid  name     salary  Comments
1      raj      30000   raj's salary is less than dev, sunil but greater than prashant, anil, dilip.
2      prashant 24935   prashant's salary is less than raj, dev, anil, sunil but greater than dilip.
3      dev      38572   dev's salary is less than sunil but greater than raj, prashant, anil, dilip.
4      anil     29845   anil's salary is less than raj, dev, sunil but greater than prashant, dilip.
5      sunil    48201   sunil has the highest salary.
6      dilip    12093   dilip has the lowest salary.
``````

SQL Fiddle: http://sqlfiddle.com/#!3/2ebca/63/0