January 10, 2008 at 12:05 pm
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
January 10, 2008 at 12:23 pm
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
*/
January 10, 2008 at 12:35 pm
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 10, 2008 at 1:20 pm
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
January 10, 2008 at 1:22 pm
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
January 10, 2008 at 1:42 pm
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 10, 2008 at 1:52 pm
Thanks Grant for the info.
sg2000
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply