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 |
|
helixpoint
Constraint Violating Yak Guru
291 Posts |
 Posted - 2009-06-18 : 13:14:32
|
| I have this convoluted sql statement. It brings back the models latest’s pictures. The problem is, the model keeps entering pics and she them has just her pics in the latest section. I would like to do a distinct on the modelid and just bring one pic per model. How can I do this?SELECT modelSet_1.modelsSetName, modelSet_1.modelID, modelPic_1.modelPic, modelPic_1.modelPicDefault, modelPic_1.modelPicsID, modelSet_1.modelsSetID, modelPic_1.modelPicMain, ShoppingCart_1.DateAdded, modelSet_1.Price, models_1.firstName, models_1.alias, models_1.heightFeet, models_1.heightInches, models_1.weight, models_1.bust, models_1.waist, models_1.hipsFROM modelSet AS modelSet_1 INNER JOIN models AS models_1 ON modelSet_1.modelID = models_1.modelID INNER JOIN modelPic AS modelPic_1 ON modelSet_1.modelsSetID = modelPic_1.modelsSetID LEFT OUTER JOIN ShoppingCart AS ShoppingCart_1 ON modelSet_1.modelsSetID = ShoppingCart_1.modelsSetIDWHERE (modelSet_1.isMainSet = 0) AND (modelSet_1.AvailDate < GETDATE()) AND (models_1.isActive = 1) AND (models_1.isHomePage = 1) AND (modelPic_1.modelPicDefault = 1) AND (modelPic_1.modelPicMain = 1) AND (modelSet_1.isSold = 0) AND (modelSet_1.isActive = 1) AND (ShoppingCart_1.DateAdded IS NULL)DaveHelixpoint Web Developmenthttp://www.helixpoint.com |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-06-18 : 13:32:56
|
| The quick and dirty way would be to use the ROW_NUMBER() function. But, do you need to do all the joins and return all that data for just one picture per model (including cart data)? If not, simplifying the query would help. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-18 : 14:27:24
|
| http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx |
|
|
|
|
|
|
|
|
Distinct question