Get Latest Records per ID (Maximum Date) in Bold BI Using Custom SQL Query

Overview

This article explains how to retrieve only the most recent rows for each unique ID in your Bold BI dataset by using a Custom SQL Query in Code View. This approach is ideal for showing the latest status, order, or activity per entity in Grid widgets and other visualizations, while correctly handling ties (multiple records with the same maximum date).

Prerequisites

• A data source with:
  • An ID column (e.g., Id, CustomerId, RecordID)
  • A date/datetime column (e.g., Date, OrderDate, UpdatedDate)
• Administrator or Editor permission to modify data sources.
• Supported database (SQL Server, PostgreSQL, MySQL, etc.).

Step-by-Step Instructions

Step 1. Open Data Sources

• Sign in to Bold BI.
• Click the Data Sources icon in the left navigation.

Step 2. Edit the Existing Data Source

• Locate and edit the data source containing your table.

Step 3. Switch to Code View (Custom Query)

• In the Data Designer, switch from Design View to Code View.

Step 4. Write the Custom SQL Query

Replace the existing query with the following pattern (adapt table/column names to yours):

SELECT
    t."Id",
    t."Date",
    t."Description",
    -- Add other columns you need
FROM "your_table_name" AS t
WHERE t."Date" = (
    SELECT MAX(t2."Date")
    FROM "your_table_name" AS t2
    WHERE t2."Id" = t."Id"
); 

• The inner query finds MAX(Date) for the current Id.
• The outer query returns every row where Date equals that maximum.
• If multiple rows share the same maximum date for an ID, all of them are returned.

Click Run to preview the results.

Step 5. Click on the Update button and confirm the preview shows:

• only rows for the maximum date per ID
• all rows when multiple records share the same maximum date (ties)
  
  

Step 6. Click Save to update the data source and navigate to your existing dashboard (or create a new one).

Step 7. Validate the behavior in a Dashboard widget.

• Add a widget (for example, Grid). Click on the settings (gear) icon and navigate to the Assign Data section.
  
  
• Bind the widget to the required fields of the updated data source.
  
  
• Click on Preview to verify the widget displays the expected latest records.
  
  
  
  
  

Conclusion

Using a custom SQL query to retrieve the latest record per ID based on the maximum date is an effective approach for ensuring accurate and up-to-date insights in Bold BI. By structuring your query correctly, you can eliminate outdated entries and focus only on the most recent data points. This enhances dashboard reliability and supports better decision-making. Implementing this method within your data source setup ensures that your visualizations always reflect the latest information without requiring additional post-processing.

Related Articles

• Creating and editing data sources in Bold BI
• Using Code View (Custom Query) in Bold BI
• Using Grid widgets and binding data fields in Bold BI