Select primary key where max value in group clase SQL Server

Question

How I can do select a primary key in grouping by clause, and it will return max value from another table which not in group by clause? For example :

Table A :

ID table_b_id Value
1  1          100
2  1          200
3  1          150
4  2          300
5  2          200
6  2          100
7  3          100
8  3          200

Table B

ID Name
1  A
2  B
3  C

Result Expected

B.ID B.Name A.ID
1    A       2   
2    B       4   
3    C       8

I've try some queries like this :

select b.id, max(b.name), max(a.id) as kd_rec
from table_a a join table_b
on a.table_b_id = b.id
group by b.id

I don't know how to get max value from table a group by b.

You do realize that table B is unused in your required result, right? — Amit, Jul 24 '15 at 07:30
possible duplicate of [T-SQL: Selecting Column Based on MAX(Other Column)](http://stackoverflow.com/questions/3680254/t-sql-selecting-column-based-on-maxother-column) — Amit, Jul 24 '15 at 07:31
@AMIT : I'm sorry, the table is recreate for easy understanding. Actually the table and join more complex than that, join around 6 tables, and there is some information needed in that table that I didn't include in query. I just want to know how to do that query, you can make it as `select b.id,max(b.name), max(a.id) as kd_rec` — Jun Rikson, Jul 24 '15 at 07:46

Ullas · Accepted Answer · 2015-07-24T07:50:11.390

1

If you don't want the Name from tableB, then

Query

;with cte as
(
  select rn=row_number() over
  (
    partition by table_b_id
    order by [Value] desc
  ),*
  from tableA
)
select table_b_id as [B.ID],
ID as [A.ID]
from cte 
where rn=1;

Fiddle demo

If you want the Name also in the result set, then

Query

;with cte as
(
  select rn=row_number() over
  (
    partition by table_b_id
    order by [Value] desc
  ),*
  from tableA
)
select t1.table_b_id as [B.ID],
t2.Name as [B.Name],
t1.ID as [A.ID]
from cte t1
join tableB t2
on t1.table_b_id=t2.ID
where t1.rn=1;

Fiddle demo

edited Jul 24 '15 at 07:50

answered Jul 24 '15 at 07:40

Ullas

11,450
4
33
50

sorry bro, is this `with cte as` for creating temporary table? – Jun Rikson Jul 24 '15 at 07:55
Its a temporary result set. – Ullas Jul 24 '15 at 07:58
I feel that the query about 6 times slower bro, but this do the job. Is there any can I do for optimize this? Btw, I will mark your answer. – Jun Rikson Jul 24 '15 at 07:59
1

@ColourDalnet The other option to do this is to use outer apply is something like this: "outer apply (select top 1 * from tableA a where a.id = b.id order by value desc)". No idea if it will perform any better in your actual case though. – James Z Jul 24 '15 at 08:42

Select primary key where max value in group clase SQL Server

1 Answers1