Left join with limit

Posted: August 3, 2011 in MySql
test=# select * from master;
 id
----
  1
  2
(2 rows)

test=# select * from detail;
 id | val
----+-----
  1 | 200
  2 | 200
  1 | 100
(3 rows)


This is the left join:

test=# select m.id, d.val from master m left join detail d on m.id=d.id;
 id | val
----+-----
  1 | 100
  1 | 200
  2 | 200
(3 rows)


But you need only one row from detail, which? Suppose, this one with the
max(val) value:

test=# select m.id, d.val from master m left join (select id, max(val)
as val from detail group by id) d on m.id=d.id;
 id | val
----+-----
  1 | 200
  2 | 200
(2 rows)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s