SQL - nested select queries

Question

DATA SETS

I want to select all dealercode (distinct) and introducercode with iapn. Bbut iapn must be a highest value only.

e.g.

dealercode = 7 and iapin = 3 and introducercode = 3

like that (THIS MUST BE A ONLY VALUE FOR DEALERCODE)

This is my query:

  SELECT DISTINCT 
      dealercode, iapin as iapin,
      (SELECT introducercode) as introducecode
  FROM 
      dealerplacement d
  where 
      exists (SELECT MIN(iapin) FROM dealerplacement )
  ORDER BY 
      dealercode, iapin ASC

I have attached with this post data set to this code.

enter image description here

This is the structure. I want to get result as below. DEALERCODE 1200 iapn 003 and introducercode 203. only.

current database as below.

  1200  001  201
  1200  002  202
  1200  003  203

Please help me to solve this.

Adriaan Stander · Accepted Answer · 2012-07-27 06:02:11Z

4

Why not just use MAX and GROUP BY or is there something else I am missing

SELECT  dealercode,
        introducercode,
        MAX(iapin) iapin
FROM    dealerplacement d
GROUP BY    dealercode,
            introducercode

Aggregate Functions (Transact-SQL)

EDIT

Re the changes to your request, you might want to try something like

SELECT  d.*
FROM    dealerplacement d INNER JOIN
        (
            SELECT  dealercode, 
                    MAX(iapin) iapin 
            FROM    dealerplacement d 
            GROUP BY    dealercode
        ) dM    ON  d.dealercode = dM.dealercode
                AND d.iapin = dM.iapin

edited Jul 27, 2012 at 6:02

answered Jul 27, 2012 at 5:33

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adriaan Stander Over a year ago

Can you may post your data in your question, and also, what are your expectations regarding the introducercode?

TRS Over a year ago

I added post as answer.please check it.

Zane Bien · Accepted Answer · 2012-07-27 06:08:13Z

1

It seems like you want the MINIMUM iapin based on your example result. To get the proper corresponding introducecode, you'll need to use a subselect:

SELECT a.dealercode, a.iapin, a.introducecode
FROM dealerplacement a
INNER JOIN
(
    SELECT dealercode, MIN(iapin) AS miniapin
    FROM dealerplacement
    GROUP BY dealercode
) b ON a.dealercode = b.dealercode AND a.iapin = b.miniapin

answered Jul 27, 2012 at 6:08

Zane Bien

23.2k6 gold badges47 silver badges58 bronze badges

Collectives™ on Stack Overflow

SQL - nested select queries

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related