Finding min and max

[Fim]

Code Select Expand


MIN_MAX_START:
    TEMP_MIN = 100
    TEMP_MAX = -100

MIN_MAX_SELECT:
    SQL_SATS = "SELECT TEMP FROM TEMPERATUR WHERE DATE LIKE " + $SQ + 2017% +$SQ
    slSel(SQL_SATS)

MIN_MAX_READING:
    IF slGetRow = 0 THEN EXIT FUNCTION
    TEMP_=  VAL(slFN("TEMP" ))

MIN_OCH_MAX_LAGRING:
    TEMP_MAX = MAX(TEMP_MAX, TEMP)
    TEMP_MIN = MIN(TEMP_MIN, TEMP)
    GOTO MIN_MAX_READING


Is it possible to solve this in an SQL statement only?
/Fim

[Bern Ertl]

I think you can use CASE WHEN to do it.  Something like this:

"SELECT (CASE WHEN TEMP < " + FORMAT$( TEMP_MIN) + " THEN TEMP ELSE " + FORMAT$( TEMP_MIN) + " END) AS MinTemp,
            (CASE WHEN TEMP > " + FORMAT$( TEMP_MAX) + " THEN TEMP ELSE " + FORMAT$( TEMP_MAX) + " END) AS MaxTemp
   FROM TEMPERATUR ...

[Fim]

Thanks.
I will have a look at it.
/Fim W.

[cj]

What does the create statement look like?
What does your select statement look like?
Do you want a value for each record?
Do you want the record with the lowest and highest temperature?

No data to test, no program to test.

This returns MIN and MAX temperature in table
$Now = "datetime('now','localtime')"
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
LOCAL Temp AS LONG
LOCAL sql AS STRING
slOpen "junk.db3","C
slexe "drop table if exists t1"
slexe "create table if not exists t1(Date,Temp INTEGER)"
FOR Temp = -400 TO 200 STEP 100
  sql = "insert into t1 values(" + $Now + "," + USING$("###)",temp)
  slexe sql
NEXT
Viewer "select min(temp) AS MIN, max(temp) AS MAX from t1"
END FUNCTION

FUNCTION Viewer(sql AS STRING) AS STRING
DIM sArray() AS STRING
slSelAry sql,sArray(),"Q9"
? JOIN$(sArray(),$CR),%MB_SYSTEMMODAL,FUNCNAME$
END FUNCTION

[Bern Ertl]

Based upon Fim's OP, I understood that he wanted to pull a single temperature value from a specific row, but have SQL compare the value to a min and max value (that is known in the code, not the database) and return the comparison results.

If the intention is to pull the MIN and MAX values from a range of rows, there is definitely a more efficient way to do it using SQLite's MIN() and MAX() functions.

[cj]

I'd have to understand the question better so I flunk this test.

MIN_MAX_START:
    TEMP_MIN = 100
    TEMP_MAX = -100

MIN_MAX_SELECT:
    SQL_SATS = "SELECT TEMP FROM TEMPERATUR WHERE DATE LIKE " + $SQ + 2017% +$SQ
    slSel(SQL_SATS)

MIN_MAX_READING:
    IF slGetRow = 0 THEN EXIT FUNCTION
    TEMP_=  VAL(slFN("TEMP" ))

MIN_OCH_MAX_LAGRING:
    TEMP_MAX = MAX(TEMP_MAX, TEMP)
    TEMP_MIN = MIN(TEMP_MIN, TEMP)
    GOTO MIN_MAX_READING

[Fim]

Yes my intention is to pull the MIN and MAX values from a range of rows using SQL, but how?

/Fim W

[cj]

Is this thread closed?

Yes my intention is to pull the MIN and MAX values from a range of rows using SQL, but how?

/Fim W

select min(temperature), max(temperature) from MyTable Where ......

Have no idea what the range of records is.  By year?  Between two dates?
Do you only want a 1-line recordset containing the lowest and highest temperature?

This is an example of getting lowest and highest temperature for any year and restricts temperature within a range.

REM $Now = "datetime('now','localtime')"  'minmax.bas
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
LOCAL year,temperature,LowTemp,HighTemp AS SINGLE
LOCAL sql AS STRING
slOpen "junk.db3","C
slexe "drop table if exists t1"
slexe "create table if not exists t1(Date,temperature INTEGER)"

'insert temperatures in 2017 and 2018
FOR temperature = 1 TO 10
  InsertRecord "2017-01-02",temperature + 50  'insert temperatures 51 to 60 in 2017
  InsertRecord "2018-12-31",temperature       'insert termeratures 1  to 10 in 2018
NEXT

'show everything
sql = "select rowid,* from t1 order by date"
Viewer sql

'search restricting to this range
Year = 2018
LowTemp  = 1  'probably don't need this
HighTemp = 10 'probably dont't need this

sql = "select min(temperature) AS MIN, max(temperature) AS MAX from t1 " +_
       "where strftime('%Y',Date) ='" + FORMAT$(Year) + "' and temperature between "+FORMAT$(LowTemp) + " and " + FORMAT$(HighTemp)
Viewer sql
END FUNCTION

FUNCTION InsertRecord(sDate AS STRING,temperature AS SINGLE) AS LONG
LOCAL s AS STRING
s = "insert into t1 values('" + sDate + "'," + FORMAT$(temperature) + ")"
slexe s
END FUNCTION

FUNCTION Viewer(sql AS STRING) AS STRING
DIM sArray() AS STRING
slSelAry sql,sArray(),"Q9"
? sql + STRING$(2,$CR) + JOIN$(sArray(),$CR),%MB_SYSTEMMODAL,FUNCNAME$
END FUNCTION