January 11, 2012 at 4:07 pm
Hi:
I have a quick question, a little gray on syntax I think. The query below works great.
SELECT TOP 1000 [PN]
,[linkedtable].[DrawNum]
,[linkedtable].[RevisionNum]
,[Description]
,[SolidWorksAttribute]
,[InVantage]
,[SWModel]
,[SWDrawing]
,[VDrawing]
FROM [CarbonFiber].[dbo].[IM]
INNER JOIN [LinkedServer].[SharePointProgress].[dbo].[tblPartRev] as linkedtable
ON PN = [linkedtable].[PartNum]
ORDER BY PN
This query on the other hand does not. How can I get the max value only, or do I need to do a select within a select?
SELECT TOP 1000 [PN]
,[linkedtable].[DrawNum]
,MAX([linkedtable].[RevisionNum])
,[Description]
,[SolidWorksAttribute]
,[InVantage]
,[SWModel]
,[SWDrawing]
,[VDrawing]
FROM [CarbonFiber].[dbo].[IM]
INNER JOIN [LinkedServer].[SharePointProgress].[dbo].[tblPartRev] as linkedtable
ON PN = [linkedtable].[PartNum]
ORDER BY PN
Thanks in advance!
January 11, 2012 at 4:11 pm
Does a massive GROUP BY on the rest of the columns not give you the expected results?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 11, 2012 at 4:14 pm
Will a group by eliminate the old values? In this case, I have several revisions (A, B, C... etc...), and would only want C to show up in the result.
Thanks!
January 11, 2012 at 4:30 pm
steve.anderson 7639 (1/11/2012)
Will a group by eliminate the old values? In this case, I have several revisions (A, B, C... etc...), and would only want C to show up in the result.Thanks!
It should, but without sample data and schema hard to be sure from this end of the internet. Try putting this between the ON and the ORDER BY:
GROUP BY [PN]
,[linkedtable].[DrawNum]
,[Description]
,[SolidWorksAttribute]
,[InVantage]
,[SWModel]
,[SWDrawing]
,[VDrawing]
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 12, 2012 at 8:55 am
No go...
Msg 8120, Level 16, State 1, Line 4
Column 'LinkedServer.SharePointProgress.dbo.tblPartRev.RevisionNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
January 12, 2012 at 12:29 pm
SELECT TOP (1000)
IM.PN,
iTVF.DrawNum,
iTVF.RevisionNum,
IM.[Description],
IM.SolidWorksAttribute,
IM.InVantage,
IM.SWModel,
IM.SWDrawing,
IM.VDrawing,
FROM CarbonFiber.dbo.IM AS IM
CROSS APPLY
(
SELECT TOP (1)
LinkedTable.DrawNum,
LinkedTable.RevisionNum
FROM LinkedServer.SharePointProgress.dbo.tblPartRev AS LinkedTable
WHERE
LinkedTable.PartNum = IM.PN
ORDER BY
LinkedTable.RevisionNum DESC
) AS iTVF
ORDER BY
IM.PN
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 12, 2012 at 3:00 pm
Amazing, but I'm sure you knew that. Do you recommend a good reference for understanding all of this syntax?
Thanks!
January 12, 2012 at 9:16 pm
That's very kind of you. The main syntax element there is APPLY, for which a reasonable two-part article is available on this site:
http://qa.sqlservercentral.com/articles/APPLY/69953/
http://qa.sqlservercentral.com/articles/APPLY/69954/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply