how can I change this query so that it only gives me the first result?

SELECT 
        AssemblyItem.islotitem AS parentlot ,
        AssemblyItem.custitem3 as sku,
        AssemblyItem.ID AS AssemblyItemID,
        (SELECT islotitem FROM Item WHERE Item.id = AssemblyItemMember.Item )AS  Memberlot,
        AssemblyItemMember.Item AS memeberid,
        BUILTIN.DF( AssemblyItemMember.MemberUnit ) AS MemberItemUOM,
        inventoryItemLocations.item,
        AssemblyItemMember.Quantity AS MemberItemQty,
        inventoryItemLocations.quantityavailable,
        
        FROM
        Item AS AssemblyItem
        INNER JOIN AssemblyItemMember ON
        ( AssemblyItemMember.ParentItem = AssemblyItem.ID )
        INNER JOIN inventoryItemLocations ON AssemblyItem.ID  = inventoryItemLocations.item  AND inventoryItemLocations.location = 14
        WHERE   AssemblyItem.itemtype = 'Assembly' AND inventoryItemLocations.quantityavailable >0

https://timdietrich.me/blog/netsuite-suiteql-query-pagination/ You can wrap it in this and only get rownum 1

I didnt make myself clear I need a distinct /first AssemblyItemID

You can use

DISTINCT

and

first_value

with a window function. Example:

SELECT DISTINCT basecurrency, transactioncurrency,
       first_value(currencyrate.exchangerate) OVER (PARTITION BY basecurrency, transactioncurrency ORDER BY effectivedate DESC, currencyrate.id DESC) AS exchangerate
FROM   currencyrate

it doesnt work still getting both

I know Microsoft SQL has a TOP clause where you can use SELECT TOP 1 basecurrency... I'm not sure about Oracle or SuiteQL.

if there are 10 assemblies it will return 1 assembly then

Dense rank and then put it in the where

Oh, I see. No, the TOP clause returns only the first record out of all results. You may need a GROUP BY clause after the WHERE clause. EDIT It looks like you need to aggregate AssemblyItemMember.Quantity somehow...if you have 2 members, then you'll get 2 results per assembly.

DENSE_RANK() OVER (ORDER BY AssemblyItem.ID) AS rank_within_group , this returns 1 for both

Your first screen shot in these replies, you have Assembly Item 28597, and each row has a different MemberID(26774, 26776). It's the 2 distinct MemberID's that are causing 2 rows for the Assembly.

I know but I only want the first

Will you always want that specific ID 26774?

yes

Then, wouldn't you want each Member ID as you break it down?

since some of them are lot numbered assemblies i need to know the MemberItemQty

Got it, so at the end of your WHERE clause you can add "AND AssemblyItemMember.Item = 26774"

how does that help

This may be above my skill level, I'm sorry if I wasted your time.

never consider it a waste time every question is a learning experience for both people