• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

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
0
freshstartusa
Asked:
freshstartusa
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select case when price1 > price2 then price1 else price2 end as Greatest from yourtabel
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now