Get only the Maximum value directly from source

[Skovgaard]

Hi Experts,

Hope you can help.
In below picture, I'm trying to filter the field DXFVDT directly in the source, to only return the maximum value.

When I try with below code, only the headlines is returned, no values.
Can anybody help with this?

SELECT OGDMTX.DXFVDT, OGDMTX.DXOBV1

FROM M3.M3FDBPRD.OGDMTX OGDMTX

WHERE (OGDMTX.DXDISY='DA') AND (OGDMTX.DXOBV1='42850050') AND (OGDMTX.DXFVDT = (SELECT MAX(OGDMTX.DXFVDT) FROM M3.M3FDBPRD.OGDMTX OGDMTX))

/Skovgaard

 

[RoryA]

I'd imagine you need a WHERE clause in the SELECT MAX part too.

 

[Skovgaard]

I'd imagine you need a WHERE clause in the SELECT MAX part too.

Well...
Below code gives me only the maximum as wanted, but as soon as I try to implement another filter, e.g. OGDMTX.DXOBV1='42850050', it gives an empty table.
How should the AND function be build together with the SELECT MAX?

SELECT OGDMTX.DXFVDT, OGDMTX.DXOBV1

FROM M3.M3FDBPRD.OGDMTX OGDMTX

WHERE OGDMTX.DXFVDT = (SELECT MAX(OGDMTX.DXFVDT) FROM M3.M3FDBPRD.OGDMTX OGDMTX)

/Skovgaard

 

[RoryA]

Do you have an entry for 42850050 that matches the max value of the whole table?

 

[Skovgaard]

Do you have an entry for 42850050 that matches the max value of the whole table?

Argh... Rookie mistake Still in early SQL learning stage...

Below code works...

SELECT OGDMTX.DXFVDT, OGDMTX.DXOBV1

FROM M3.M3FDBPRD.OGDMTX OGDMTX

WHERE (OGDMTX.DXOBV1='110') AND (OGDMTX.DXFVDT = (SELECT MAX(OGDMTX.DXFVDT) FROM M3.M3FDBPRD.OGDMTX OGDMTX))

Is it possible to re-write the code, so I find the max value for entry 42850050?
I think...
Step 1: Find all 42850050
Sted 2: Find the max value

/Skovgaard

 

[RoryA]

Depending on what your data source is, you could do something like ordering the results in descending date order and then taking the first result - for example:

SELECT TOP 1 OGDMTX.DXFVDT, OGDMTX.DXOBV1

FROM M3.M3FDBPRD.OGDMTX OGDMTX

WHERE (OGDMTX.DXDISY='DA') AND (OGDMTX.DXOBV1='42850050') ORDER BY OGDMTX.DXFVDT DESC

 

[Skovgaard]

Depending on what your data source is, you could do something like ordering the results in descending date order and then taking the first result - for example:

SELECT TOP 1 OGDMTX.DXFVDT, OGDMTX.DXOBV1

FROM M3.M3FDBPRD.OGDMTX OGDMTX

WHERE (OGDMTX.DXDISY='DA') AND (OGDMTX.DXOBV1='42850050') ORDER BY OGDMTX.DXFVDT DESC

This gives me below error:
I appreciate your help!

/Skovgaard

 

[RoryA]

I'm not familiar with that data source, but based on a quick google you might need something like:

SELECT OGDMTX.DXFVDT, OGDMTX.DXOBV1
FROM M3.M3FDBPRD.OGDMTX OGDMTX
WHERE (OGDMTX.DXDISY='DA') AND (OGDMTX.DXOBV1='42850050') ORDER BY OGDMTX.DXFVDT DESC
fetch first 1 row only

 

[Skovgaard]

I'm not familiar with that data source, but based on a quick google you might need something like:

SELECT OGDMTX.DXFVDT, OGDMTX.DXOBV1
FROM M3.M3FDBPRD.OGDMTX OGDMTX
WHERE (OGDMTX.DXDISY='DA') AND (OGDMTX.DXOBV1='42850050') ORDER BY OGDMTX.DXFVDT DESC
fetch first 1 row only

I've testet, and it only gives me one row, the maximum date is in many rows.

I've also google through a lot of SQL functions and tried many things. Below is what I finally constructed, which works as intended.

SELECT OGDMTX.DXFVDT, OGDMTX.DXOBV1

FROM M3.M3FDBPRD.OGDMTX OGDMTX
WHERE (OGDMTX.DXDISY='DA' AND OGDMTX.DXOBV1='42850050')
AND OGDMTX.DXFVDT = (SELECT MAX(OGDMTX.DXFVDT)
FROM M3.M3FDBPRD.OGDMTX OGDMTX
WHERE OGDMTX.DXDISY='DA' AND OGDMTX.DXOBV1='42850050')

Any way, thanks Rory for guiding me through this educational experience

/Skovgaard

 

[RoryA]

the maximum date is in many rows

Ah - my fault for assuming there would only be one match! Anyway, glad you got it resolved.