How to find largest value in a subquery
-
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
)
-
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
-
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.
-
SELECTa.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 -
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 1, Cast(Rand() * 100 AS INT)
UNION ALL 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)
UNION ALL SELECT 2, Cast(Rand() * 100 AS INT)
UNION ALL SELECT 2, Cast(Rand() * 100 AS INT)
UNION ALL SELECT 2, 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply