How can I merge supplier records without using GROUP BY or aggregate functions in SQL Server?

Data and Databases
1

Need a SQL Server query that combines rows for supplier 1 without GROUP BY or aggregation. Below is an alternative sample:

WITH TempData AS (
  SELECT 
    COALESCE(Inv.altID, Inv.ID) AS EntryID,
    Inv.ID AS OldEntry,
    Inv.prodName,
    Inv.prodSize,
    Inv.supRef,
    Inv.qty
  FROM ProdInventory Inv
)
SELECT t.EntryID, t.OldEntry, S.supplierName, t.prodName, t.prodSize, t.qty
FROM TempData t
JOIN Suppliers S ON t.supRef = S.supID
WHERE t.supRef = 1;
2

hey, you might try a cross apply with a correlating subquery to pull just one row per supplier. works as a neat device to sidestep group by and agrregates. its a bit hacky but does the trick, esp if you know the data structure well.

3

One method that has worked well for me involves using a subquery with the TOP clause alongside an ORDER BY to pull a single record per supplier before joining with other tables. This approach can effectively bypass the need for GROUP BY or aggregate functions. By incorporating a derived table where you explicitly filter for the first matching record based on a unique key, you can merge supplier records in a controlled way. This solution requires careful attention to the ORDER BY clause to ensure the correct record is selected, which has proven reliable in my experience.

4

hey, try using a wndowing query with row_number() partitioned by supplier id to select one row per supplier. its neat and doesn’t use group by. have u tried this method? any thoughts?

5

hey, another idea is to use a self join: left join the table to itself and filter rows that have no smaller id. its a clever way to pick just one row per supplier without grp by and aggrgates. might suit your needs!