0

I have a table with a list of account numbers.

I am joining this table with one that has account owner's.

Tha question is that some account may have more that 1 owner, so my query return some duplicated results.

Is there a function to return the "first" matching results and ignore the second owner?

FábioRB
  • 335
  • 1
  • 12
  • You could try a `CROSS_APPLY` in conjunction with a `TOP 1`, as answered here: https://stackoverflow.com/questions/25399878/easiest-way-to-limit-join-to-only-select-top-1 However, you may find this slow on larger tables. – Oaty Jul 28 '22 at 23:20
  • Provide a sample of your input tables, the expected output and tag the question with your `DBMS`, doing so we can help you better. – ahmed Jul 28 '22 at 23:38
  • I did not provide the sample code because it was a simple join (one table with numeric account number field) and other with 2 columns, account number + account owner (name string) – FábioRB Jul 29 '22 at 00:55

1 Answers1

2

I'm making guesses as to what your schema looks like as you did not share it.

select  *
from    Accounts a
        join Users u on a.Id = u.AccountId
where   u.id in (select min(x.id) from Users x group by x.AccountId)

You could get better answers if you share your schema (at least the relevant parts) and what SQL Engine you're using as some have special features to make this even more straightforward.

BVernon
  • 3,205
  • 5
  • 28
  • 64