How to join to table row with max column value in a View

Question

Post reply

How to join to table row with max column value in a View

sg2000-429020

Ten Centuries

Points: 1046
More actions
January 10, 2008 at 12:05 pm

#202215

I have a View defined as:
SELECT dbo.TabA.Col1, dbo.TabB.Col2
FROM dbo.TabA LEFT OUTER JOIN
dbo.TabB ON dbo.TabB.ForgnKey = dbo.TabA.PrimKey
In this case, I got all of TabB col2 on the View result. However, I only want the View to display the row of TabB where its Col2 value is max. (obviously, I don't want to display the rows with NULL).
How can I do this? I tried using the MAX function but it won't work. Plesae help. Thanks in advance.
sg2000

Viewing 7 posts - 1 through 6 (of 6 total)

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

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 1

Perhaps something like:

declare @tabB table (Col1 int, Col2 int)

insert into @tabB

select 1, 4 union all

select 2, 9 union all

select 3, 8 union all

select 4, 9

select * from @tabB

where col2 = (select max(col2) from @tabB)

/* -------- Sample Output: --------

Col1 Col2

----------- -----------

2 9

4 9

*/

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

sg2000 (1/10/2008)
I have a View defined as:
SELECT dbo.TabA.Col1, dbo.TabB.Col2
FROM dbo.TabA LEFT OUTER JOIN
dbo.TabB ON dbo.TabB.ForgnKey = dbo.TabA.PrimKey
In this case, I got all of TabB col2 on the View result. However, I only want the View to display the row of TabB where its Col2 value is max. (obviously, I don't want to display the rows with NULL).
How can I do this? I tried using the MAX function but it won't work. Plesae help. Thanks in advance.
sg2000

SELECT dbo.TabA.Col1, dbo.TabB.Col2

FROM dbo.TabA

LEFT JOIN dbo.TabB

ON dbo.TabB.ForeignKey = dbo.TabA.PrimaryKey

AND dbo.TabB.Col2 = (SELECT TOP 1 t.Col2

FROM dbo.TabB t

WHERE t.ForeignKey = dbo.TabB.ForeignKey

ORDER BY t3.Col DESC)

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

sg2000-429020 Ten Centuries Points: 1046 More actions · Answer 3

Grant:

Thanks very much for the quick response. Yes, it works!!!! I just wonder why TOP 1 works and not MAX. Well, may be I can find out later on. Thanks again.

sg2000

sg2000-429020 Ten Centuries Points: 1046 More actions · Answer 4

Kent:

Thanks for the quick response. However, I can't figure out how to fit what you recommend in a View? Anyway, I got it working by following the suggestion from Grant (the next poster).

sg2000

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

Depending on the data type & how you write the query, the MAX should work too. I've just found that the TOP... ORDER BY works better especially with a clustered index.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

sg2000-429020 Ten Centuries Points: 1046 More actions · Answer 6

sg2000-429020

Ten Centuries

Points: 1046

January 10, 2008 at 1:52 pm

#766865

Thanks Grant for the info.

sg2000