Solved

Using Min (like Excel and Access) in SQL

Posted on 2003-11-13
8
338 Views
Last Modified: 2006-11-17
I am trying to run a query and one of the fields I want to use a min statement like a can do in Excel or Access that would take the minimum value of two fields.

min (number1, number2)
0
Comment
Question by:cruzer330
[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
  • 2
  • 2
  • 2
8 Comments
 
LVL 19

Expert Comment

by:Dexstar
ID: 9745118
cruzer330:

> I am trying to run a query and one of the fields I want to use a min statement
> like a can do in Excel or Access that would take the minimum value of two fields.

> min (number1, number2)

Do it like this:

   SELECT CASE WHEN Number1 > Number2 THEN Number2 ELSE Number1 END as TheValue FROM YourTable

It isn't as pretty, but it is a lot more powerful.

Hope That Helps,
Dex*
0
 

Author Comment

by:cruzer330
ID: 9745303
That is what I was thinking to use only I was trying to find a quick way.  The problem being that what I want to do when I find the minimum is assign a letter corresponding to the lowest of the two numbers.  This can be done in the Select Case When ... Else ... End but I would be performing the same operation in the THEN section and the ELSE section.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9745971
You could write your own SQL User Defined Function if you really want to.

0
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 

Author Comment

by:cruzer330
ID: 9747491
Don't know how to do that.  Any suggestions for a quick tutorial?
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 50 total points
ID: 9747657
Run this in query analyzer:


CREATE FUNCTION MINVALUE(@Value1 INT, @Value2 INT)

RETURNS INT

AS

BEGIN
DECLARE @ReturnValue INT

  IF @Value1 > @Value2
    @ReturnValue = @Value2

  IF @Value1 < @Value2
    @ReturnValue = @Value1

RETURN(@ReturnValue)
END



Then run this in Query Analyzer to test:

SELECT dbo.MINVALUE(1,19)


and it should return 1

I have not got too tricky with the coding as I don't have SQL installed here so I can't test it.
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9749099
>The problem being that what I want to do when I find the minimum is assign a letter corresponding to the lowest
> of the two numbers.  This can be done in the Select Case When ... Else ... End but I would be performing the
> same operation in the THEN section and the ELSE section.

Post the code you were thinking of using.  We can probably optimize it for you.  Even if you have it in a function that you call, you're still going to be calling it twice...  :)

Dex*
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

717 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