Read side SQL: Extracting only the latest row

Translated submission in the German forum. You can read the original entry here .

Hi,

I have an MS-SQL database where the data is merged from two tables. This works fine using the following SQL script on the READ side:

Depending on the number of entries, F_RP.TP_ID can appear any number of times. However, I always only need the last entry, which I can determine from F_RP.R_DATE.

How can I get FlowHeater to just load the last entry by itself? Do I have to do that in SQL or can it be easily achieved using a Heater?

Thanks and greetings,
Nick

Hi Nick,

You can do this with the GroupBy Heater . Drag and drop the GroupBy Heater onto the Designer window and connect it to your field F_RP.TP_ID. To enable it to function properly you also have to modify the ORDER BY clause in the SQL SELECT statement. In your case, for example:

order by F_TP.T_SU, F_RP.TP_ID, F_RP.R_DATE

It is important that all "F_RP.TP_ID" fields that are to be grouped are delivered to FlowHeater in sequence. The GroupBy Heater then combines all identical values to a single row on the WRITE side. Note that if instead of the last entry, you wish to determine the first value, it is simply necessary to modify the Order by clause, by adding DESC (meaning descending) after the date field. For example:

order by F_TP.T_SU, F_RP.TP_ID, F_RP.R_DATE DESC