How to get the Min and Max in SQL SERVER

Jes Sie

Below is my table in SQL SERVER. What I want is to extract the lowest partsCost and the latest partsCost inserted into the table:

Below is my stored procedure but does not work:

ALTER PROCEDURE [dbo].[spClaims_GetLowestLatestPrice] --'honda','civic','1'
-- Add the parameters for the stored procedure here
@carMake nvarchar(50),
@carModel nvarchar(50),
@partsCode int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
with query1 as
(SELECT ISNULL(partsCost,0) AS LowestPrice
FROM claimsPartsMasterList
WHERE carMake = @carMake AND carModel = @carModel AND partsCode = @partsCode AND partsCost = (SELECT MIN(partsCost) FROM claimsPartsMasterList AS LowestPrice)),
query2 as
(SELECT ISNULL(partsCost,0) AS LatestPricePurchase
FROM claimsPartsMasterList
where carMake = @carMake AND carModel = @carModel AND partsCode = @partsCode AND getLogs = (SELECT MAX(getLogs) FROM claimsPartsMasterList AS LatestPricePurchase))
SELECT LowestPrice, LatestPricePurchase from query1, query2
END

Hope someone can help with this. Thanks in advance.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty7y ago
Accepted answer
Hey try this.
ALTER PROCEDURE [dbo].[spClaims_GetLowestLatestPrice] --'honda','civic','1'
    -- Add the parameters for the stored procedure here
        @carMake nvarchar(50),
        @carModel nvarchar(50),
        @partsCode int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

    with query1 as
    (SELECT ISNULL(MIN(partsCost),0) AS LowestPrice
    FROM claimsPartsMasterList
    WHERE carMake = @carMake AND carModel = @carModel AND partsCode = @partsCode),

    query2 as
    (SELECT ISNULL(partsCost,0) AS LatestPricePurchase
    FROM claimsPartsMasterList
    where carMake = @carMake AND carModel = @carModel AND partsCode = @partsCode
    AND getLogs = (SELECT TOP 1 getLogs FROM claimsPartsMasterList AS LatestPricePurchase
    WHERE carMake = @carMake AND carModel = @carModel AND partsCode = @partsCode))

    SELECT LowestPrice, LatestPricePurchase from query1, query2

END
0
Jes Sie7y ago
Thank you all for helping. I changed a bit the code given by Amit as follows and is now working.
with query1 as
    (SELECT ISNULL(MIN(partsCost),0) AS LowestPrice
    FROM claimsPartsMasterList
    WHERE carMake = @carMake AND carModel = @carModel AND partsCode = @partsCode),

    query2 as
    (SELECT ISNULL(partsCost,0) AS LatestPricePurchase
    FROM claimsPartsMasterList
    where carMake = @carMake AND carModel = @carModel AND partsCode = @partsCode
    AND getLogs = (SELECT TOP 1 getLogs FROM claimsPartsMasterList AS LatestPricePurchase
    WHERE carMake = @carMake AND carModel = @carModel AND partsCode = @partsCode order by Id desc  ))

    SELECT LowestPrice, LatestPricePurchase from query1, query2
I only added "order by Id desc ".

Again, my warmest thanks to all who gave this ideas.

I love this community.
0
Sonu Gupta7y ago
Hi,

Try to this -

select partsCode, max(partsCost) as MaxPartsCost, min(partsCost) as MinPartsCost from
claimsPartsMasterList group by partsCode
+1
Bhuban Magar7y ago
Hi,
Please try this once again,

with query1 as
(
ISNULL(MIN(partsCost),0) AS LowestPrice
FROM claimsPartsMasterList
WHERE carMake = @carMake AND carModel = @carModel AND partsCode = @partsCode),

query2 as
(SELECT ISNULL(partsCost,0) AS LatestPricePurchase
FROM claimsPartsMasterList
where carMake = @carMake AND carModel = @carModel AND partsCode = @partsCode AND getLogs = (SELECT MAX(getLogs) FROM claimsPartsMasterList))

SELECT query1.LowestPrice, query2.LatestPricePurchase from query1, query2

.
0