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

I know my answer is a bit late, but that might help others. There is a way to achieve a First() and Last() in SQL Server, and here it is :

Stuff(Min(Convert(Varchar, DATE_FIELD, 126) + Convert(Varchar, DESIRED_FIELD)), 1, 23, '') 

Use Min() for First() and Max() for Last(). The DATE_FIELD should be the date that determines if it is the first or last record. The DESIRED_FIELD is the field you want the first or the last value. What it does is :

1. Add the date in ISO format at the start of the string (23 characters long)
2. Append the DESIRED_FIELD to that string
3. Get the MIN/MAX value for that field (since it start with the date, you will get the first or last record)
4. Stuff that concatened string to remove the first 23 characters (the date part)

Here you go!

EDIT: I got problems with the first formula : when the DATE_FIELD has .000 as milliseconds, SQL Server returns the date as string with NO milliseconds at all, thus removing the first 4 characters from the DESIRED_FIELD. I simply changed the format to "20" (without milliseconds) and it works all great. The only downside is if you have two fields that were created at the same seconds, the sort can possibly be messy... in which cas you can revert to "126" for the format.

Stuff(Max(Convert(Varchar, DATE_FIELD, 20) + Convert(Varchar, DESIRED_FIELD)), 1, 19, '') 

EDIT 2 : My original intent was to return the last (or first) NON NULL row. I got asked how to return the last or first row, wether it be null or not. Simply add a ISNULL to the DESIRED_FIELD. When you concatenate two strings with a + operator, when one of them is NULL, the result is NULL. So use the following :

Stuff(Max(Convert(Varchar, DATE_FIELD, 20) + IsNull(Convert(Varchar, DESIRED_FIELD), '')), 1, 19, '') 

Answer 2

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