SQL query

  • 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?

  • 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.
    General Disclaimer:
    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?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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