?
Solved

Using Min (like Excel and Access) in SQL

Posted on 2003-11-13
8
Medium Priority
?
344 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 200 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

762 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