Link to home
Start Free TrialLog in
Avatar of freshstartusa
freshstartusa

asked on

Return greater of two fields

Hi There,

I am joining two tables, both have a price. I only want to return a single price field which on a record by record basis is the larger of the two.

For example:
Price 1   Price 2    Returned Price
0              7              7
2              5              5
7              1              7
9              0              9
etc.

I Need MSSQL syntax I can use in the view below. Note in the second half of the unity where I'm using a GREATEST() function. Unfortunately MSSQL doesn't have such a function!  Need the equivalent funciton or a work around so I can put something like:

GREATEST(mp.ProductPrice,pp.Price) as ProductPrice

in the SELECT clause . . .

Here's my view:
CREATE VIEW ProjectProductView
 
AS
 
 SELECT ProductID, ProductTitle, ProductName, ProductSKU ,ProductCategoryList, ProductOverview, ProductDescription, ProductMetaKeywords, ProductPrice, ProductSalePrice, ProductPetMedsPrice, ProductMainImage, ProductThumbnailImage, ProductWeight, ProductMinimumPrice, ProductProjectID, ProductID as DistinctID
 FROM CUSTOM_PRODUCT mp
 UNION ALL
 SELECT mp.ProductID, ProductTitle, ProductName, ProductSKU ,ProductCategoryList, ProductOverview, ProductDescription, ProductMetaKeywords, GREATEST(mp.ProductPrice,pp.Price) as ProductPrice, ProductSalePrice, ProductPetMedsPrice, ProductMainImage, ProductThumbnailImage, ProductWeight, ProductMinimumPrice, pp.ProjectID as ProductProjectID, (pp.ProjectID * 100000 + mp.ProductID) as DistinctID
 FROM MASTER_PRODUCT mp, PRODUCT_PROJECT pp
 WHERE pp.ProductID = mp.ProductID
 

Best Wishes,
Peter
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial