Max Number - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Max Number

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-08-07 : 12:41:12

I have the following query:

SELECT s1_wd, s2_wd, s3_wd, ss
FROM dbo.Logic
WHERE (ss = N'ss-r89-01')

that returns:

s1_wd s2_wd s3_wd ss
20 125 200 SS-R89-01

I want it to return only the highest number in one of the sX_wd columns. I this case that would be 200. How can I do this?

Thanks,
Lane

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-07 : 12:52:29

Here's a couple of ways

 
SELECT ss,CASE 
WHEN s1_wd<= s2_wd AND s1_wd<=s3_wd THEN s1_wd
WHEN s2_wd<= s1_wd AND s2_wd<=s3_wd THEN s2_wd
WHEN s3_wd<= s1_wd AND s3_wd<=s2_wd THEN s3_wd
END  AS min_s_wd
FROM dbo.Logic 
WHERE (ss = N'ss-r89-01') 



SELECT ss,
       MIN( CASE d.s_wd
                WHEN 1 THEN t.s1_wd
                WHEN 2 THEN t.s2_wd
                WHEN 3 THEN t.s3_wd
            END ) AS min_s_wd
FROM dbo.Logic as l
CROSS JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS d( s_wd )
WHERE (ss = N'ss-r89-01') 
GROUP BY l.ss

HTH
Jasper Smith

lane0618
Posting Yak Master

134 Posts

Posted - 2002-08-07 : 13:30:56

that works.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources