Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
seeker62
Starting Member
40 Posts |
 Posted - 2013-04-17 : 11:59:18
|
| I have many contracts and each one has many shipping orders. I want to develop a query that would give me one shipping order for each contract. I have usedselect so from sotable sot inner join contracttable ct on sot.contract = ct.contract where exists (select top 1 so from sotable subsot inner join contracttable subct where ct.contractnum = subct.contractnum)this still gives me all the so for the contract. only want 1 so per contract to show in query |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-17 : 12:13:21
|
quote:
Originally posted by seeker62
I have many contracts and each one has many shipping orders. I want to develop a query that would give me one shipping order for each contract. I have usedselect so from sotable sot inner join contracttable ct on sot.contract = ct.contract where exists (select top 1 so from sotable subsot inner join contracttable subct where ct.contractnum = subct.contractnum)this still gives me all the so for the contract. only want 1 so per contract to show in query
If all you want is one so per contract, this should do it. But don't you need other columns, such as which contract etc.?select max(so) from sotable group by contract |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-18 : 02:33:58
|
| [code]select your required columns...from(select *,max(so) over (partition by contract) as maxso from sotable)twhere maxso = so[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
gathering a single record