SQL: Inner Join return one row based on criteria

stedrolln34 Published at Dev

stedrolln34

This is probably simple, but i'm looking for the raw SQL to perform an INNER JOIN but only return one of the matches on the second table based on criteria.

Given two tables:

**TableOne**   
 ID  Name  
 1   abc  
 2   def

**TableTwo**  
 ID    Date     
 1     12/1/2014
 1     12/2/2014
 2     12/3/2014
 2     12/4/2014
 2     12/5/2014

I want to join but only return the latest date from the second table:

Expected Result:
1   abc   12/2/2014
2   def   12/5/2014

I can easily accomplish this in LINQ like so:

TableOne.Select(x=> new { x.ID, x.Name, Date = x.TableTwo.Max(y=>y.Date) });

So in other words, what does the above LINQ statement translate into in raw SQL?

Mureinik

You could join the first table with an aggregate query:

SELECT t1.id, d
FROM   TableOne t1
JOIN   (SELECT   id, MAX[date] AS d
        FROM     TableTwo
        GROUP BY id) t2 ON t1.id = t2.id

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-14

Comments

0 comments

From Dev

Inner join query return more than one row?

From Dev

Return one row in a view based on a left join column value

From Dev

SQL one to many with inner join

From Dev

Return maximum for each row based on multiple criteria

From Dev

Inner join return only the first matching Row

From Dev

pandas: grouping and selecting one row based on criteria

From Dev

Inner Join and Group Multiple Tables into one row

From Dev

Select one row from inner join MSSQL

From Dev

Returning one row per id with inner join

From Dev

Inner join with one row of another table

From Dev

Inner join two files based on one column in unix when row names don't match with sort

From Dev

SQL Select Inner join one by one

From Dev

How to return a group of rows when one row meets "where" criteria in SQL Anywhere

From Dev

SQL Server: Alternate Assigning a Row based on a criteria

From Dev

SQL Server : INNER JOIN returning incorrect row

From Dev

SQL inner join based on table name pattern

From Dev

Insert based on inner join oracle sql

From Dev

Inner join in SQL Server based on lookup table

From Dev

SQL inner join based on table name pattern

From Dev

SQL Adding a Column to table, based on a inner join

From Dev

SQL join in one row in a query

From Dev

SQL JOIN to Only One Row

From Dev

two inner join in one sql statement

From Dev

SQL One to many join - do not return rows from the 'one' table if ANY row in the 'many' table equals X

From Dev

Using INNER JOIN and MAX(), to return same row as MAX

From Dev

VBA copy row from one sheet to another based on 2 criteria

From Dev

mySQL - Count Distinct row based on criteria in one column

From Dev

Want to get one row when doing select distinct with inner join

From Dev

Display one row in report by concat Inner join query

Related Related

Article