MIN across multiple columns with a GROUP BY

Question

Post reply

MIN across multiple columns with a GROUP BY

kiran 4243

Mr or Mrs. 500

Points: 553
More actions
January 13, 2015 at 10:12 pm

#300196

Hi,
How to get the lowest U.Price along with FX and Rate.
ID-U.Price-FX-Rate
1280 19.1196 EUR 3.85
1280 46.2462 USD 3.63
1280 6.32 RM 1.00
Required output.
ID-U.Price-FX-Rate
1280 6.32 RM 1.00

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63517 More actions · Answer 1

Re-reading this... Welcome!

Those are ROWS, thank goodness! Across columns is NOT fun. Best way is to post consumable data...

This looks like it works:

SELECT P.Id, P.Price, P.CurType, P.Rate

FROM #Prices P

INNER JOIN

(SELECT MIN(Price) MinPrice

FROM #Prices

GROUP BY ID) M

ON P.Price = M.MinPrice;

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

you can do it in number of ways. i prefer this one because is simple and you do not need to write to many lines 🙂

Something like this

Select *

from (

select *, Row_Number() Over (partition by ID Order by uPrice) as rownum

from

(

select 1280 AS ID, 19.1196 AS uPrice , 'EUR'AS FX, 3.85 AS rate union all

select 1280 AS ID, 46.2462 AS uPrice , 'USD'AS FX, 3.63 AS rate union all

select 1280 AS ID, 6.32 AS uPrice , 'RM' AS FX, 1.00AS rate

) A

) X

Where rownum = 1

Hope it helps. In case of any query do let us know.

Eirikur Eiriksson SSC Guru Points: 182919 More actions · Answer 3

kiran 4243 (1/13/2015)
Hi,
How to get the lowest U.Price along with FX and Rate.
ID-U.Price-FX-Rate
1280 19.1196 EUR 3.85
1280 46.2462 USD 3.63
1280 6.32 RM 1.00
Required output.
ID-U.Price-FX-Rate
1280 6.32 RM 1.00

Quick simple solution

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TBL_SAMPLE_PRICE') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_PRICE;

CREATE TABLE dbo.TBL_SAMPLE_PRICE

(

ID INT NOT NULL

,U_Price NUMERIC(18,5) NOT NULL

,FX CHAR(3) NOT NULL

,Rate NUMERIC(18,5) NOT NULL

);

INSERT INTO dbo.TBL_SAMPLE_PRICE (ID,U_Price,FX,Rate)

VALUES

(1280, 19.1196 ,'EUR' ,3.85)

,(1280, 46.2462 ,'USD' ,3.63)

,(1280, 6.32 ,'RM' ,1.00);

SELECT TOP(1)

SP.ID

,SP.U_Price

,SP.FX

,SP.Rate

FROM dbo.TBL_SAMPLE_PRICE SP

ORDER BY SP.U_Price;

Results

ID U_Price FX Rate

------ --------- ---- ---------

1280 6.32000 RM 1.00000

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

I'd prefer SELECT TOP(1) ... ORDER BY too. If all rows with equal minimum value are requierd use WITH TIES option.