DISTINCT within Datasource

[haleb]

I have a report where I want to pull the first asset record that is attached to a work order only. My datasource consists of three different joins, so I am trying to avoid using a sub select. I have tried using MIN and DISTINCT within the initial select statement but neither seem to work. Is there another method to achieve this?

Below is our datasource. The field we want to return the first match on is woa.AssetID

Code: Select all

select 
wo.[EndDate],
wo.[WorkOrderID],
wo.[ID],
woa.[AssetID],
trm.*
from 
[reports].[WorkOrders] wo
Left Join [reports].[InventoryTransactions] invt On wo.[ID]=invt.[WorkOrderID]  
Left Join [reports].[WorkOrderAssets] woa On woa.[ID]=wo.[ID]
Join [reports].[WorkOrderForm_TrafficLightingRepairMaintenance] trm On trm.ID = wo.ID 
where
wo.[DepartmentName]='TPW Traffic' 
and wo.[StatusName]='Closed' 
and ((invt.[Name] like '%LED%' OR invt.[Name] like '%led%') 
and (invt.[Name] like '%Head%' OR invt.[Name] like '%head%' 
OR invt.[Name] like '%LAMP%' OR invt.[Name] like '%lamp%'))
and (cast(wo.[EndDate] as date) between @from and @to)

[Lech Kulikowski]

Hello,

Please try to use TOP in the query:
https://www.w3schools.com/sql/sql_top.asp

Thank you.

[haleb]

Hi Lech,

I tried the TOP method several different ways, in my main select and a sub select.However when selecting "Retrieve Columns" nothing is pulled. What is your suggested way of formatting TOP within my query? It is only the ASSET ID field we want the first matching record for, so I don't want to add TOP to the entire query.

[Lech Kulikowski]

Hello,

Please send us a sample report with test data that reproduces the issue for analysis.

Thank you.