TSQL nested basic query

Question

I am stuck to a T-SQL query. I think that it is basic. But I coundn't figure it out. Can someone be kind enough to shed some light on it? Thanks in advance!

Here is my question. I want to write a query to do the following tasks:

Raw Data:

Department Type Salary Age 
1 1 1000 30 
1 2 1500 31 
1 3 2000 25 
2 1 250 35 
2 2 50 20 
2 3 300 35

Ideally, I want to have a table, which have the following info:

Department Type Salary Age
1 3 2000 25
1 2 1500 31
1 1 1000 30
2 3 300 35
2 1 250 35

The selection is based on a comparison in each Department. I compare the Salary of each Type within each Department. And the ones whose Salary is larger than or equal to Type 1 in the same Department will be selected. Then within each Department, the records are ordered in a descending order on Salary.

Here is my wrong codes.

SELECT Department, Type, Salary, Age
FROM Database.dbo.Table1
WHERE Salary >=  
(
SELECT Salary
FROM Database.dbo.Table1
WHERE Type = 1
GROUP BY Department
)
GROUP BY Department

I hope the illustration is clear. Please feel free to let me know if it is not. I can explain more.

Thanks again!

Answer 1

select * from T as t1
where 
type=1
or
Exists(select 1 from t where t.[Department]=t1.[Department]
                             and
                             t.type=1
                             and 
                             salary<t1.salary 
                             )

order by [Department],salary DESC

SQLFiddle demo

Answer 2

Nothing too tricky here - just a self-join back to the same table to identify the Type = 1 Salary and then a fairly normal ORDER BY clause:

Sample data:

declare @t table (Department int,[Type] int, Salary int, Age int)
insert into @t(Department,[Type], Salary, Age ) values
(1 ,1 ,1000 ,30    ),
(1 ,2 ,1500 ,31    ),
(1 ,3 ,2000 ,25    ),
(2 ,1 ,250 ,35     ),
(2 ,2 ,50 ,20      ),
(2 ,3 ,300 ,35     )

Query:

select
    t1.*
from
    @t t1
        inner join
    @t t2
        on
            t1.Department = t2.Department and
            t1.Salary >= t2.Salary and
            t2.[Type] = 1
order by t1.Department, t1.Salary desc

You're attempt to use GROUP BY was wrong because GROUP BY doesn't have any effect¹ on the final output order.

---

¹ As a side effect of the way that grouping is processed, the output may be sorted in an order that is useful to you - but that's only a side effect and not guaranteed. The only way to guarantee ordering is with an ORDER BY.