SQL - nested select queries

Question

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.

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.

Answer 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

Answer 2

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