How to find largest value in a subquery

Question

Post reply

How to find largest value in a subquery

Russ1of5

Mr or Mrs. 500

Points: 510
More actions
October 8, 2009 at 9:49 am

#71876

Given two tables ...
Table_A
ID int
Data varchar(25)
Table_B
ID int
field_b int
Table "B" could contain (zero to many) related records from Table A
I want to be able to select the record from table_b that has the highest value for field_b
Is there a better way to do this then what I have come up with (see below) ...
SELECT
a.id,
a.data,
b.field_b
FROM
table_a as a
LEFT JOIN table_b as b ON a.id = b.id and
b.field_b =
(
select top 1 field_b
from table_b
where b.id = id
order by field_b
)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Maybe this?

SELECT

a.id,

a.data,

MAX(b.field_b) AS field_b

FROM

table_a as a

LEFT JOIN table_b as b ON a.id = b.id

GROUP BY a.id, a.data

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Russ1of5 Mr or Mrs. 500 Points: 510 More actions · Answer 2

I just tried the max. Close, but not quite working. I am getting additional records. I think the reason for this is the "Group by" clause.

I guess I over simplified my question. I have multiple columns on the select from both tables. The Group By clause brings back any rows that is not unique on table_b.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

SELECT

a.id,

a.data,

b.MAX_field_b

FROM table_a as a

LEFT JOIN (select MAX(field_b) MAX_field_b, id

from table_b

group by id

) as b ON a.id = b.id

If you need other fields from table_b you may join to it once more:

LEFT JOIN table_b b1 ON b1.id = b.id AND b1.field_b = b.MAX_field_b

_____________
Code for TallyGenerator

Dhimant Trivedi SSC Enthusiast Points: 126 More actions · Answer 4

How about this ?

DECLARE @Table_A TABLE( id INT IDENTITY ( 1 , 1 ),data VARCHAR(25) )

DECLARE @Table_B TABLE( id INT , field_b INT )

INSERT @Table_A (data)

SELECT 'a'

UNION ALL SELECT 'b'

UNION ALL SELECT 'c'

UNION ALL SELECT 'd'

UNION ALL SELECT 'e'

UNION ALL SELECT 'f'

INSERT @Table_B (id , field_b)

SELECT 1,Cast(Rand() * 100 AS INT)

UNION ALL SELECT 1, Cast(Rand() * 100 AS INT)

UNION ALL SELECT 2, Cast(Rand() * 100 AS INT)

SELECT * FROM @Table_A

SELECT * FROM @Table_B;

WITH cte

AS (SELECT a.id AS aid,

a.data,

b.id AS bid,

b.field_b,

Row_number()

OVER(PARTITION BY b.id ORDER BY b.field_b DESC) AS rownum

FROM @Table_A a,

@Table_B b

WHERE b.id = a.id)

SELECT *

FROM cte

WHERE rownum = 1

This is possible with SQL Server 2005 onwards...

Thanks,
Dhimant