How I get the maximum and minimum value in a query

[adrianzazugmail.com]

Hello Goodnight. I consult them again because for more than I try to do it, I just could not.
I want to obtain the maximum and minimum number of a query, when doing it directly to my database the sentence obtains the correct data, when I do it in ireport I do not obtain the correct data.
The query in my DB is:
select max (age)
from table_name
where date_register = '2018-08-02'
and field_name like 'Jhon'
and last_name = 15
I wrote it in different ways, in a dataset with a table and it shows me a list of the same items as in the detail band. Place directly the max field of the dataset and tell me that you can not find the field. Incerte the query in the main report query in this way:
select max (age), table.name as name, table.lastname as lastname ... etc
from table_name
where date_register = '2018-08-02'
and field_name like 'Jhon'
and last_name = 15
group by yhe_same_fields_of_select
order by age
and I get the value of the first row.
I tried to use a variable with calculation lowest and highest and it says null,
to the same variable I put an initial value expression $ f {age} and I returned the first value of the first row.
If you can help me by telling me how I can get the maximum and minimum value in a query I will be very grateful.

 

[joseng62]

Ok solution and outcome depends on what your want to achive at the end of the day. 
So if you want your database to return 1 row with min and max then you can make use of sub queries in your select statement.  You did not mention which DB you are using. 
So example in Oracle:
select ( select MAX(number_column) from Your_table ) as MaxNo,
( select MIN(number_column)from Your_table ) as MinNo
From Your_table where Rownum < 2

This would return 1 row with MaxNo column with max value and MinNo with min value. 

You could use ORDER BY age without max function and the first row would be your max and last your min, almost as you did above, just without max function and group by. 

Or if you want jasper to do this, you need to understand that each row returned from DB will be iterated through. So 3 variables. I created a report below that connect to oracle datasource and use Dual to stump data. You need to update the datasource to your DB or update sql if using another DB. 


<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.4.0.final using JasperReports Library version 6.4.1  -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report name" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="af88d3eb-c1fe-4d76-9a0c-e7394dc2ddb4">
    <property name="ireport.zoom" value="3.4522712143931042"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="UPDATE_HERE"/>
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <style name="style1" isPdfEmbedded="true"/>
    <queryString>
        <![CDATA[select * FROM 
(select 10 as age , 'Green' as Name from DUAL union
select 15 as age , 'Red' as Name from DUAL union
select 25 as age , 'Yellow' as Name from DUAL union
select 2 as age , 'Blue' as Name from DUAL )]]>
    </queryString>
    <field name="AGE" class="java.lang.Integer"/>
    <field name="NAME" class="java.lang.String"/>
    <variable name="Max" class="java.lang.Integer" calculation="Highest">
        <variableExpression><![CDATA[$V{Variable_2} > $F{AGE} ? $V{Variable_2} : $F{AGE}]]></variableExpression>
        <initialValueExpression><![CDATA[0]]></initialValueExpression>
    </variable>
    <variable name="Min" class="java.lang.Integer" calculation="Lowest">
        <variableExpression><![CDATA[$V{Variable_2} > $F{AGE} ? $V{Variable_2} : $F{AGE}]]></variableExpression>
        <initialValueExpression><![CDATA[0]]></initialValueExpression>
    </variable>
    <variable name="Variable_2" class="java.lang.Integer" calculation="Highest">
        <variableExpression><![CDATA[$V{Variable_2}]]></variableExpression>
        <initialValueExpression><![CDATA[0]]></initialValueExpression>
    </variable>
    <background>
        <band splitType="Stretch"/>
    </background>
    <detail>
        <band height="50">
            <textField>
                <reportElement x="190" y="10" width="100" height="30" uuid="9a16ad60-3e8b-415a-b0b7-3b024797f48e"/>
                <textFieldExpression><![CDATA[$V{Max}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="450" y="10" width="100" height="30" uuid="8949c50d-a9db-4f93-b5c3-0a4503acfd37"/>
                <textFieldExpression><![CDATA[$V{Variable_2}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="320" y="10" width="100" height="30" uuid="224af22d-cb1a-4922-9d27-6c62bbaf7478"/>
                <textFieldExpression><![CDATA[$V{Min}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="10" y="10" width="100" height="30" uuid="41a78580-9280-4134-ad54-7cbb69a906e5"/>
                <textFieldExpression><![CDATA[$F{AGE}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <summary>
        <band height="100" splitType="Stretch"/>
    </summary>
</jasperReport>
 

[adrianzazugmail.com]

Thanks joseng62 for your answer, I use DB postgreSQL, I did something similar to what you tell me, now I will try to do them as you tell me.
One more question, in Ireport to create a vartiable, in options has the field calculation and one option is lower and another Highest, these options serve my purpose?
On the other hand I would like a tip too: When using a sub query, would it be better to do it separately or in the main head?
My idea is the following:
/ ************************************************ ****************************** /
Page Header
                                           here: $ {variable1} max = min? "max": "max-min"
/ ************************************************ ***************************** /
I want to put a field, that write in value of maximum-minimum and if they are equal, only the value. But as I have tried it gives me a table with all the values or it gives me an erroneous value.
First of all, Thanks.

PD: The cogigo that you put me, I'm analyzing it and I'm going to adapt it to use it. Thank you.