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

Are you looking for max(fila) or max(date)? Both will work for the sample given, but I'd like to know to be sure. — shree.pat18
– shree.pat18, Commented Apr 13, 2014 at 9:11
I need max(fila) from each id from table2 and combine with data from table1, Thanks — atty
– atty, Commented Apr 13, 2014 at 9:20
OK, thanks for clarifying that. You could try my answer and let me know if it works for you. — shree.pat18
– shree.pat18, Commented Apr 13, 2014 at 9:21

sarin · Accepted Answer · 2014-04-13 09:24:47Z

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

edited Apr 13, 2014 at 9:24

answered Apr 13, 2014 at 8:51

sarin

5,3173 gold badges39 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

shree.pat18 Over a year ago

You'll need one more join to get the fila value.

atty Over a year ago

I need to obtain the max(fila) from each id from table 2, and combine the result with data from table1: name, address):

sarin Over a year ago

@atty try that instead.

shree.pat18 · Accepted Answer · 2014-04-13 08:49:02Z

0

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

answered Apr 13, 2014 at 8:49

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

2 Comments

atty Over a year ago

Do you know how to make a sql view from this solution?

shree.pat18 Over a year ago

You might want to take a look at this: sqlusa.com/bestpractices2005/viewwithcte

Reza · Accepted Answer · 2014-04-13 08:51:53Z

0

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

answered Apr 13, 2014 at 8:51

Reza

20.1k18 gold badges102 silver badges176 bronze badges

Comments

Azadeh Radkianpour · Accepted Answer · 2014-04-13 09:02:28Z

0

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

edited Apr 13, 2014 at 9:02

answered Apr 13, 2014 at 8:57

Azadeh Radkianpour

9709 silver badges19 bronze badges

1 Comment

atty Over a year ago

doesn't work.I nedd max(fila) from each id in table2 combine with data on table 1

Collectives™ on Stack Overflow

How to use SQL(Max) function

4 Answers 4

3 Comments

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related