SQL scripting 1

[scevbact]

I have two SQL tables with a one to many relationship. I will join on the common field . What I want to do is to pull the common field from one table and find the First, or top value in a filed from the "many table". Any help would be appreciated. Thanks

 

[bborissov]

SELECT *
FROM OneTable
INNER JOIN (SELECT *, ROW_NUMBER() OVER (PARTITION BY CommonField ORDER BY WhatEverYouWant) AS RC
                   FROM ManyTable) ManyTable ON OneTable.CommonField = ManyTable.CommonFIeld AND ManyTable.RC = 1

Borislav Borissov
VFP9 SP2, SQL Server

 

[scevbact]

Thank you Borislav. What would I put in the parenthesis in ROW()?

 

[bborissov]

nothing

Borislav Borissov
VFP9 SP2, SQL Server

 

[gbaughma]

Borislav, do you have a link to somewhere I could read about the PARTITION stuff? I have the worst time wrapping my brain around it... I'm probably over-complicating it.


Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg