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

**URL:** <https://techcommunity.softwareag.com/t/sql-gateway-how-to-do-it-list-of-values-determine-min-max/202688>\
**Category:** Forum\
**Tags:** Adabas-Natural, Adabas, Adabas-Add-on-Products, SQL-Access, Mainframe-Integration, CONNX\
**Created:** [February 4, 2019, 4:42pm UTC](https://techcommunity.softwareag.com/t/sql-gateway-how-to-do-it-list-of-values-determine-min-max/202688 "2019-02-04T16:42:11Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![ChristianHeld](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/christianheld/32/138_2.png) [@ChristianHeld](https://techcommunity.softwareag.com/u/ChristianHeld)\
**Post date:** [February 4, 2019, 4:42pm UTC](https://techcommunity.softwareag.com/t/sql-gateway-how-to-do-it-list-of-values-determine-min-max/202688/1 "2019-02-04T16:42:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Guest](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/guest/32/75_2.png) [@Guest](https://techcommunity.softwareag.com/u/Guest)\
**Post date:** [February 28, 2019, 6:19pm UTC](https://techcommunity.softwareag.com/t/sql-gateway-how-to-do-it-list-of-values-determine-min-max/202688/2 "2019-02-28T18:19:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Bill\_Luton1](https://avatars.discourse-cdn.com/v4/letter/b/71e660/32.png) [@Bill\_Luton1](https://techcommunity.softwareag.com/u/Bill_Luton1)\
**Post date:** [February 22, 2019, 8:15pm UTC](https://techcommunity.softwareag.com/t/sql-gateway-how-to-do-it-list-of-values-determine-min-max/202688/3 "2019-02-22T20:15:50Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Bill\_Luton1](https://avatars.discourse-cdn.com/v4/letter/b/71e660/32.png) [@Bill\_Luton1](https://techcommunity.softwareag.com/u/Bill_Luton1)\
**Post date:** [February 22, 2019, 8:22pm UTC](https://techcommunity.softwareag.com/t/sql-gateway-how-to-do-it-list-of-values-determine-min-max/202688/4 "2019-02-22T20:22:15Z")

</div>

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

thanks,  
Bill

---

<div class="post-metadata">

**Author:** ![ChristianHeld](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/christianheld/32/138_2.png) [@ChristianHeld](https://techcommunity.softwareag.com/u/ChristianHeld)\
**Post date:** [March 18, 2019, 12:05pm UTC](https://techcommunity.softwareag.com/t/sql-gateway-how-to-do-it-list-of-values-determine-min-max/202688/5 "2019-03-18T12:05:36Z")

</div>

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?

```auto

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.

---

<div class="post-metadata">

**Author:** ![Bill\_Luton1](https://avatars.discourse-cdn.com/v4/letter/b/71e660/32.png) [@Bill\_Luton1](https://techcommunity.softwareag.com/u/Bill_Luton1)\
**Post date:** [March 25, 2019, 5:41pm UTC](https://techcommunity.softwareag.com/t/sql-gateway-how-to-do-it-list-of-values-determine-min-max/202688/6 "2019-03-25T17:41:56Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\
**Post date:** [April 2, 2021, 3:42pm UTC](https://techcommunity.softwareag.com/t/sql-gateway-how-to-do-it-list-of-values-determine-min-max/202688/7 "2021-04-02T15:42:15Z")

</div>


