Solved

Using Min (like Excel and Access) in SQL

Posted on 2003-11-13
8
305 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

929 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now