How to get unique values in one column while selecting multiple columns in SQL Server?

Data and Databases
1

I’m trying to get unique values in a single column while selecting multiple columns in SQL Server. Here’s what I’ve tried:

SELECT DISTINCT(OrderID), OrderDate, Description, TotalAmount, SupplierName,
       OrderStatus, CustomerCharge, TaskType, LocationName, LocationID,
       ReferenceCode, PaymentInfo, AccountNumber, TotalExpense
FROM OrderSummary

This didn’t work as expected. I also tried using GROUP BY:

SELECT OrderID, OrderDate, Description, TotalAmount, SupplierName,
       OrderStatus, CustomerCharge, TaskType, LocationName, LocationID,
       ReferenceCode, PaymentInfo, AccountNumber, TotalExpense
FROM OrderSummary
GROUP BY OrderID

But this requires using aggregate functions for other columns, which I want to avoid.

My actual query is more complex, involving JOINs and WHERE conditions. How can I get distinct values for just the OrderID column while selecting all other columns without using aggregate functions? Any help would be appreciated!

2

In SQL Server, you can use the ROW_NUMBER() function in combination with a subquery to achieve what you’re looking for. Here’s an approach that should work:

WITH RankedOrders AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY (SELECT NULL)) AS RowNum
    FROM OrderSummary
)
SELECT OrderID, OrderDate, Description, TotalAmount, SupplierName,
       OrderStatus, CustomerCharge, TaskType, LocationName, LocationID,
       ReferenceCode, PaymentInfo, AccountNumber, TotalExpense
FROM RankedOrders
WHERE RowNum = 1

This method assigns a row number to each row within partitions of the same OrderID. By selecting only rows where RowNum = 1, you get distinct OrderIDs while keeping all other columns. This approach works well with complex queries involving JOINs and WHERE conditions, and doesn’t require aggregate functions for non-grouped columns.

3

hey there! have you tried using a subquery with ROW_NUMBER()? it’s super handy for this kinda thing. something like:

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY (SELECT NULL)) AS rn
FROM OrderSummary
) t
WHERE rn = 1

what do you think? does that help with your complex query? let me know if you need more details!

4

hey zoestring42, try a CTE with ROW_NUMBER(). e.g:

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderDate) AS rn
FROM OrderSummary
)
SELECT * FROM cte WHERE rn = 1

This should get you a distinct OrderID while keeping all columns. hope it helps!