TSQL nested basic query
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!
No comments:
Post a Comment