Solved

Using Min (like Excel and Access) in SQL

Posted on 2003-11-13
8
294 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help on Setting up an identical test database 17 53
Mssql SQL query 14 28
sql calculate averages 18 30
Change part of a string 2 24
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

21 Experts available now in Live!

Get 1:1 Help Now