SQL Gateway / how to do it / List of values, determine MIN, MAX

, , , , ,
1

Hi

I need a hint, maybe someone can give an advice

I have a SELECT Statement, the structure is:

SELECT xyz FROM table where col in ( ). The is specified by the end user.

What I need is the MAX( ) and MIN( )
Basically, I want to manipulate the as a set.

Ex: ::= 2016,2018,2015,2019, MAX() would be 2019, MIN() would be 2015

My investigations so far show this to work

Creating a temp table and use it like this.

SELECT MAX(YEAR), MIN(YEAR)
FROM (SELECT 2016 AS YEAR UNION ALL SELECT 2018 UNION ALL SELECT 2015 UNION ALL SELECT 2019) TEMP

As I have to handle a list of values with a variable number of elements, this approach is not really manageable.

Does anyone has an idea how to implement this?

Regards Chris

2

Hi Chris,

Is this what you are looking for?

select min(a.year), max(a.year) from (select xyz, col as year from

where col in ()) a

I set up a simple test and it returned the min and max values out of my in list.

Thanks,
Bill Luton
R&D Director
CONNX Engineering
Software AG

3

Hi Chris,

Will this do what you are looking for?

select min(a.year) as minYear, max(a.year) as maxYear from (select xyz, col as year from

where col in ()) a

I built a simple test and got back the min and max values from my in-list.

Thanks,
Bill Luton
R&D Director
CONNX Engineering
Software AG

4

The should be followed by two right parentheses. The tool stuck an emoji in the middle of the syntax. Please disregard that.

thanks,
Bill

5

Hi Bill

Sorry for the delayed feedback on your proposal, I had to handle a bereavement.

Looking at your SELECT Statement, I understood your approach.
But what about

and the XYZ column?


SELECT
  MIN(A.YEAR), MAX(A.YEAR)
FROM (SELECT XYZ, COL AS YEAR FROM  TABLE WHERE COL IN (2015,2016,2020)) A

I tried the SQL but I always get Syntax Errors. Simply because table is not defined.
Maybe your solution is too tricky for me.

6

Hi Chris,

was meant to denote whatever your table name is. The xyz was just a possible arbitrary column. I saw you had it in the query from your original question so I left it in. It is not necessary for this query, however. The query can be

SELECT
MIN(A.YEAR), MAX(A.YEAR)
FROM (SELECT COL AS YEAR FROM TABLE WHERE COL IN (2015,2016,2020)) A

Please note that you need to substitute your own table name and column names. i.e. COL needs to be the column in your table that contains the year and TABLE needs to be your actual table name. The IN list will also be provided by your users as you stated in the original question.