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 |
|
chris_wood99
Yak Posting Veteran
70 Posts |
 Posted - 2011-06-29 : 09:04:36
|
why is this not just pulling out info for the records in the subquery ?SELECT * FROM VersionObjVersion_Bak29Jun11 v1WHERE EXISTS (select obj_id, max(version) versionFROM VersionObjVersion_Bak29Jun11 v2where v1.obj_id = v2.obj_idAND v2.version = v1.versiongroup by obj_id)its simple, but ive been on a long holiday and my brain is now mush  |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-29 : 09:12:24
|
| Because there is always a row for the obj_id and version in the outer query (unless one is null)You probably want something likeSELECT * FROM VersionObjVersion_Bak29Jun11 v1WHERE version =(select max(version) versionFROM VersionObjVersion_Bak29Jun11 v2where v1.obj_id = v2.obj_id)==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
theboyholty
Posting Yak Master
226 Posts |
Posted - 2011-06-29 : 09:13:29
|
| Its because the data in your SELECT * part isn't linked by a common field to your WHERE EXISTS clause.If your WHERE EXISTS returns ANY data at all, the top part runs and pulls ALL the data from VersionObjVersion_Bak29Jun11 but if the EXISTS returns nothing, then the top part won't run.---------------------------------------------------------------------------------http://www.mannyroadend.co.uk The official unofficial website of Bury Football Clubhttp://www.hollowtreewebdesign.co.uk - a web design company in its infancy |
|
|
|
chris_wood99
Yak Posting Veteran
70 Posts |
Posted - 2011-06-29 : 10:29:09
|
| cheers,was being dense...nigel yours was spot on, I really need to brush up my T-SQL ! |
|
|
|
|
|
|
exists