Solved

Using Min (like Excel and Access) in SQL

Posted on 2003-11-13
8
329 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

679 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