SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Author Topic: Finding min and max  (Read 661 times)

Fim

  • Guru
  • ***
  • Posts: 153
    • View Profile
    • Fims sajt
Finding min and max
« on: August 04, 2018, 06:19:00 AM »

Code: [Select]
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
Logged
Fim W

Bern Ertl

  • Master Geek
  • ****
  • Posts: 496
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Finding min and max
« Reply #1 on: August 04, 2018, 05:23:45 PM »

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

  • Guru
  • ***
  • Posts: 153
    • View Profile
    • Fims sajt
Re: Finding min and max
« Reply #2 on: August 05, 2018, 02:59:42 AM »

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

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Re: Finding min and max
« Reply #3 on: August 05, 2018, 08:20:44 AM »

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
Logged
CJ

Bern Ertl

  • Master Geek
  • ****
  • Posts: 496
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Finding min and max
« Reply #4 on: August 06, 2018, 08:33:31 AM »

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.
« Last Edit: August 06, 2018, 08:35:25 AM by Bern Ertl »
Logged

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Re: Finding min and max
« Reply #5 on: August 06, 2018, 08:50:15 AM »

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
Logged
CJ

Fim

  • Guru
  • ***
  • Posts: 153
    • View Profile
    • Fims sajt
Re: Finding min and max
« Reply #6 on: August 06, 2018, 11:15:13 AM »

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

/Fim W
Logged
Fim W

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Re: Finding min and max
« Reply #7 on: August 07, 2018, 10:44:21 AM »

Is this thread closed?

Quote
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
« Last Edit: August 09, 2018, 07:27:33 AM by cj »
Logged
CJ