Aggregate SQL Function to grab only the first from each group

Question

I have 2 tables - an Account table and a Users table. Each account can have multiple users. I have a scenario where I want to execute a single query/join against these two tables, but I want all the Account data (Account.*) and only the first set of user data (specifically their name).

Instead of doing a "min" or "max" on my aggregated group, I wanted to do a "first". But, apparently, there is no "First" aggregate function in TSQL.

Any suggestions on how to go about getting this query? Obviously, it is easy to get the cartesian product of Account x Users:

 SELECT User.Name, Account.* FROM Account, User
 WHERE Account.ID = User.Account_ID

But how might I got about only getting the first user from the product based on the order of their User.ID ?

Answer 1

use 'select top 1'

Answer 2

SELECT (SELECT TOP 1 Name 
        FROM User 
        WHERE Account_ID = a.AccountID 
        ORDER BY UserID) [Name],
       a.*
FROM Account a

Answer 3

Rather than grouping, go about it like this...

select
    *

from account a

join (
    select 
        account_id, 
        row_number() over (order by account_id, id) - 
            rank() over (order by account_id) as row_num from user
     ) first on first.account_id = a.id and first.row_num = 0

Answer 4

There are a number of ways of doing this, here a a quick and dirty one.

Select (SELECT TOP 1 U.Name FROM Users U WHERE U.Account_ID = A.ID) AS "Name,
    A.*
FROM Account A

Answer 5

Define "First". What you think of as first is a coincidence that normally has to do with clustered index order but should not be relied on (you can contrive examples that break it).

You are right not to use MAX() or MIN(). While tempting, consider the scenario where you the first name and last name are in separate fields. You might get names from different records.

Since it sounds like all your really care is that you get exactly one arbitrary record for each group, what you can do is just MIN or MAX an ID field for that record, and then join the table into the query on that ID.