Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL query Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2014 2:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 9, 2014 6:12 AM
Points: 1, Visits: 5

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?
Post #1597979
Posted Wednesday, July 30, 2014 3:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 3,901, Visits: 8,833
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.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1598000
Posted Thursday, July 31, 2014 10:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 322, Visits: 825
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



Post #1598376
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse