Alternate query for subquery with MAX function

• sgkarthikkumar

  SSC Veteran

  Points: 205

  More actions

  January 6, 2010 at 2:33 am

  #138063

  Hi,

  The query I tried is as follows:

  SELECT * FROM TABLE1 WITH (NOLOCK)

  Where

  TABLE1.COLUMN1 in ( 4420,4697 )

  AND TABLE1.COLUMN2 in

  (SELECT COLUMN1 FROM

  (SELECT max(COLUMN2) as Key

  From TABLE2 WITH (NOLOCK) group by COLUMN3)A,

  TABLE2 WITH (NOLOCK) WHERE A.Key = TABLE2.COLUMN2AND COLUMN4 = 'SD2' AND COLUMN5 <> '')

  Now I want to reduce two sub-queries into single query. Is it possible to get the same result?

  If anybody knows please reply to me......

  Thanks in Advance.

• NitinPatel31

  Hall of Fame

  Points: 3721

  More actions

  January 6, 2010 at 2:52 am

  #1099421

  One possible alternative could be

  SELECT TABLE1.* FROM TABLE1

  INNER JOIN (

  SELECT TABLE2.COLUMN1

  FROM ( SELECT MAX(TABLE2.COLUMN2) AS [Key] FROM TABLE2

  --WITH (NOLOCK)

  GROUP BY TABLE2.COLUMN3 ) AS A,

  TABLE2

  --WITH (NOLOCK)

  WHERE A.[Key] = TABLE2.COLUMN2 AND TABLE2.COLUMN4 = 'SD2' AND TABLE2.COLUMN5 <> '']

  ) AS B ON B.COLUMN1 = TABLE1.COLUMN2

  --WITH (NOLOCK)

  WHERE TABLE1.COLUMN1 IN ( 4420,4697 )

  - uncomment NOLOCK if you need.

  And also send the table structure with some sample datarow, to get he exact result.

  Regards,
  Nitin