July 30, 2014 at 2:39 pm
Hello,
I need advice on how to do SQL queries that I returned the following:
I have 2 tables: customer and the department
-----------
SELECT a.id, a.first_name, a.last_name, MIN (b.income) b.department
/* --b.department can not be in GOUP BY clause clause, but I need to know which department has the
--smallest income, ie which department is responsible MIN (b.income) * /
FROM CUSTOMERS a
INNER JOIN department b
ON a.id = b.id
GROUP BY a.id, a.first_name, a.last_name;
How can I do it?
July 30, 2014 at 3:13 pm
The common way is to use ROW_NUMBER() within a CTE or subquery.
WITH CTE AS(
SELECT a.id,
a.first_name,
a.last_name,
b.income,
b.department,
ROW_NUMBER() OVER( PARTITION BY a.id ORDER BY b.income) rn
FROM CUSTOMERS a
INNER JOIN department b ON a.id = b.id
)
SELECT id,
first_name,
last_name,
income,
department
FROM CTE
WHERE rn = 1;
If you want ties, you could use RANK().
Feel free to ask any questions that you need to fully understand the code. 😉
July 31, 2014 at 10:38 am
I like Luis' answer. A different approach, likely with a different query plan which may be more or less efficient depending on your data/indexes, etc is this:
SELECT a.id,
a.first_name,
a.last_name,
b.income,
b.department
FROM CUSTOMERS a
CROSS APPLY (SELECT TOP 1 b.income, b.department FROM department b WHERE b.id = a.id ORDER BY b.income DESC) AS b
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply