Selecting the details of records with maximum orderID for a given fileID

Question

For the customers with the same file ID, I am trying to select only those who have the maximum file ID. However, I keep getting error:

subquery returned more than 1 value.

I have tried filtering using where clause but it is not working.

SELECT orderid, fileid, customername
FROM customer
WHERE orderID = (select max(orderID) from customer 
GROUP BY fileid)

Have you searched for `[tsql] subquery returned more than 1 value`? Tip: Reasons not to use images are [here](http://meta.stackoverflow.com/a/285557/92546). — HABO, Sep 08 '19 at 21:34
Yes, I did. I edited the code to be : SELECT max(orderid), fileid, customername FROM customer GROUP BY fileid, customername But this is not filtering any data. May be because of the unique customer name. — Bina, Sep 08 '19 at 21:40
I could solve this using CTE. However, I was wondering if there is any other way to do so. With cte as (select max(orderid) as orderid, fileid from customer group by fileid) select * from cte left join customer on cte.orderid=customer.orderid — Bina, Sep 08 '19 at 21:50
Welcome to stackoverflow. Please take a minute to take the [tour], especially How to [Ask]. Please read the accepted answer of ["Why not upload images of code on SO when asking a question?"](https://meta.stackoverflow.com/a/285557/3094533). Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it and your desired results as formatted text. For more details, [read this.](https://dba.meta.stackexchange.com/q/2976/68798) — Zohar Peled, Sep 09 '19 at 05:45

score 0 · Accepted Answer · answered Sep 09 '19 at 05:42

This can be resolved using a simple row_number and top 1 with ties.

First, create and populate sample table (Please save us this step in your future questions)

DECLARE @customer AS TABLE
(
    fileid int, 
    orderid int, 
    customername varchar(20)
);

INSERT INTO @customer (fileid, orderid, customername) VALUES
(1, 1, 'Marshall, John'),
(1, 2, 'Mary, Rose'), -- Shuold be selected
(2, 3, 'Marvis, Emma'),
(2, 4, 'Miller, Ross'), -- Shuold be selected
(4, 1, 'Saraf, Ram'), -- Shuold be selected
(5, 2, 'Miller, Katie'),
(5, 3, 'Miller, Abby'),
(5, 4, 'Wolowiz, Andy'); -- Shuold be selected

The query:

SELECT TOP 1 WITH TIES fileid, orderid, customername
FROM @Customer
ORDER BY ROW_NUMBER() OVER(PARTITION BY fileid ORDER BY orderid DESC)

Results:

fileid  orderid customername
1       2       Mary, Rose
2       4       Miller, Ross
4       1       Saraf, Ram
5       4       Wolowiz, Andy

Thank you for showing me another way to accomplish this. I could also accomplish this using: SELECT max(orderid) as MaxOrder, FileID,CustomerName FROM Customer Group By FileID, CustomerName I used the same code yesterday and wasn't working. But today, it worked like a charm. Thank you. — Bina, Sep 09 '19 at 20:05

Selecting the details of records with maximum orderID for a given fileID

1 Answers1