Help in SQL query with MAX function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help in SQL query with MAX function

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2010-05-12 : 08:05:31

Hello All,

I have to write a code in my sproc to get the low and high temperatures for selected PK records. But there are more than one records fr the PKs. I need to get only one row that matches the below criteria.

Take the difference of lowtemp-maxtemp and get that row where difference is greater. If two rows found with the same difference then get the max(mintemp). If the max(mintemp) is same then get any one row since they both are same.
If there are no records in the table for that PK columns then use existing function to get the default low and high temp.


InsCode     LowTemp     HighTemp      Difference
F20             80      85                  5
F20             85      98                  13(select this
F20             90      92                  2


InsCode     LowTemp     HighTemp      Difference
F23             90      100                10 (select this)
F23             85      95                  10 
F23             89      92                  3

If no records found for the InsCode then exec function to get default values.

Can somene help me to write query for this requirement?

Thanks in advance,
-P

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-12 : 08:24:58

Try this:


DECLARE @InsCode VARCHAR(3);

IF NOT EXISTS(SELECT *
                FROM YourTable
               WHERE InsCode = @InsCode)
   SELECT InsCode, LowTemp, HighTemp, HighTemp - LowTemp AS Difference
     FROM (SELECT @InsCode AS InsCode, ufn_GetDefaultLowTemp() AS LowTemp, ufn_GetDefaultHighTemp() AS HighTemp) AS T;
ELSE
   SELECT InsCode, LowTemp, HighTemp, Difference
     FROM (SELECT InsCode, LowTemp, HighTemp, Difference, ROW_NUMBER() OVER(PARTITION BY InsCode ORDER BY Difference DESC, LowTemp DESC) AS row_num
             FROM (SELECT InsCode, LowTemp, HighTemp, HighTemp - LowTemp AS Difference
                     FROM YourTable) AS T) AS T
    WHERE InsCode = @InsCode
      AND row_num = 1

Here @InsCode to be replaced by your procedure parameter.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 10:21:58

[code]
SELECT InsCode,
LowTemp,
HighTemp,
HighTemp- LowTemp AS Difference
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY InsCode ORDER BY (HighTemp - LowTemp) DESC,LowTemp DESC) AS Seq,*
FROM Table t
WHERE InsCode = @InsCode
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2010-05-13 : 16:12:46

Thank you to both of you. This code worked. I implemented it in my sp.

Thanks again for helping me.
-P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources