?
Solved

Return greater of two fields

Posted on 2006-06-21
1
Medium Priority
?
306 Views
Last Modified: 2010-08-05
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
Comment
Question by:freshstartusa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 375 total points
ID: 16953110
select case when price1 > price2 then price1 else price2 end as Greatest from yourtabel
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question