Skip to main content
simply more productive

The Data Specialist

Thanks to our intuitive transformation designer, you can convert data effortlessly to a variety of formats without knowing how to program. It’s not only efficient, it’s also easy to use.
Flowheater Download

Read side SQL: Extracting only the latest row

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Admin
  • Admin
More
13 years 6 months ago #2024 by FlowHeater-Team
Read side SQL: Extracting only the latest row was created by FlowHeater-Team
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:
Code:
SELECT F_TP.T_SU, F_TP.T_ME, F_RP.R_ME, F_RP.R_DATE, F_RP.TP_ID FROM LoTr.dbo.F_RP F_RP, LoTr.dbo.F_TP F_TP WHERE F_RP.CAT_ID = F_TP.CAT_ID AND F_RP.F_ID = F_TP.F_ID AND F_RP.TP_ID = F_TP.TP_ID AND ((F_TP.T_STATUS=1) AND (F_RP.R_ME Not Like '%WV%' And F_RP.R_ME Not Like '%II%' And F_RP.R_ME Not Like '%VA%')) ORDER BY F_TP.T_SU

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

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Please Log in to join the conversation.

  • FlowHeater-Team
  • Topic Author
  • Offline
  • Admin
  • Admin
More
13 years 6 months ago #2025 by FlowHeater-Team
Replied by FlowHeater-Team on topic Re:Read side SQL: Extracting only the latest row
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

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Please Log in to join the conversation.

Time to create page: 0.254 seconds

FlowHeater - The Data Specialist

Efficient data integration and transformation with FlowHeater – the perfect solution for a seamless transfer of your data.

Rechtliches

Support & Contact

Kontaktinformation

Telefon:0951 / 99339792 E-Mail:This email address is being protected from spambots. You need JavaScript enabled to view it.

Copyright © 2009-2024 by FlowHeater GmbH. All rights reserved.