How to use SQL(Max) function

Question

I have 2 tables

Table 1:

id     name      adress 
1      John      New York
2      Jane      London`

... and so on

Table 2:

id    fila      date
1     43        01/01/2010
1     39        10/01/2011
1     55        23/12/2012
2     10        01/01/2008
2     15        02/02/2010`

.... and so on

I want to get data like this

id  fila   name     adress       date
-----------------------------------------
1   55     John    New York    23/12/2012
2   15     Jane    London      02/02/2010

..... and so on.

Thanks

Answer 1

ok. what you are really looking for is "What is the latest date in table2 for each of my rows in Table1". So to answer the question:

select *
From Table1
inner join (
    select id, max(fila) as maxfila
    from Table2
    group by id
) as maxdates
on Table1.id = maxdates.id
inner join Table2 on Table2.id = maxdates.id AND Table2.fila = maxdates.maxfila

Answer 2

Try this:

;with cte as
 (select id, max(fila) maxfila
  from table2
  group by id)
 select t1.id, t1.name, t1.address, t2.fila, t2.date
 from table1 t1 
 left join table2 t2 on t1.id = t2.id
 inner join cte c on t1.id = c.id
 where t2.fila = c.maxfila

Answer 3

Try this

Select t1.id, t1.name, t1.address, t2.maxfila
 from table1 t1 
  left outer join 
  (select id, max(fila) maxfila
  from table2
  group by id) t2

Answer 4

select t1.id, t1.name t1.address, max(t2.fila), 
(select top 1 date from table2 order by fila desc where table2.id = t1.id)
 from table1 t1 inner join 
 table2 t2 on t1.id = t2.id