CODEWITHSUNDEEP
sqlsql-server
Jeff Beagley
Jeff Beagley

Reputation: 1025

Subquery or Join a table where a certain value is minimum

I have a query that has an ID in it, that is reference in multiple rows in a second table. I need to join both tables, but only select the columns from the second table where a certain column is MIN. Caveat to this is I also need to select other columns from tableb, and not just the minimum value. I need to reference the other columns that are relative to that minimum value.

Psuedo code due to code and data being confidential. 

SELECT tablea.id, tableb.id, tableb.name FROM tablea
LEFT JOIN ( SELECT * FROM tableb WHERE `id` = Minimum )

Microsoft SQL Server

Upvotes: 0

Views: 67

Answers (2)

HereGoes
HereGoes

Reputation: 1320

You could try this...

SELECT tablea.id, tableb.id, tableb.name 
FROM tablea a, tableb b
WHERE a.id = b.id AND b.amt =  (SELECT MIN(z.amt) FROM tableb z 
                                       WHERE z.id = a.id )

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

SELECT a.id, b.id, b.name 
FROM tablea a
LEFT JOIN ( SELECT *, row_number() over(partition by id order by dollar_amt_column) as rn 
           FROM tableb ) b
on a.id = b.id and b.rn = 1

Assuming you need the minimum value of id column, you could use row_number and join it to the other table.

Upvotes: 0

Related Questions