Función SQL agregada para capturar solo el primero de cada grupo

Sé que mi respuesta es un poco tarde, pero eso podría ayudar a otros. Hay una manera de lograr un First () y Last () en SQL Server, y aquí está:

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

Use Min() para First() y Max() para Last(). El DATE_FIELD debe ser la fecha que determina si es el primer o el último registro. El DESIRED_FIELD es el campo que desea el primer o el último valor. Lo que hace es :

1. Agregue la fecha en formato ISO al comienzo de la cadena (23 caracteres de largo)
2. Añadir el DESIRED_FIELD a esa cadena
3. Obtenga el valor MIN/MAX para ese campo (ya que comienza con la fecha, obtendrá el primer o el último registro)
4. Cosas que concatenan la cadena para eliminar los primeros 23 caracteres (la parte de fecha)

Aquí tienes!

EDITAR: Tengo problemas con la primera fórmula: cuando el DATE_FIELD tiene.000 como milisegundos, SQL Server devuelve la fecha como cadena sin milisegundos en absoluto, eliminando así los primeros 4 caracteres del CAMPO DESEADO. Me simplemente cambió el formato a " 20 " (sin milisegundos) y funciona muy bien. El único inconveniente es que si tiene dos campos que se crearon en los mismos segundos, la ordenación puede ser desordenada... en las cuales ca puede volver a "126" para el formato.

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

EDICIÓN 2 : Mi intención original era devolver la última (o la primera) fila NO NULA. Me preguntaron cómo devolver la última o primera fila, sea nula o no. Simplemente agregue un ISNULL al CAMPO DESIRED_FIELD. Al concatenar dos cadenas con un operador+, cuando uno de ellos es NULL, el resultado es NULL. Así que usa lo siguiente:

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

Select *
From Accounts a
Left Join (
    Select u.*, 
    row_number() over (Partition By u.AccountKey Order By u.UserKey) as Ranking
    From Users u
  ) as UsersRanked
  on UsersRanked.AccountKey = a.AccountKey and UsersRanked.Ranking = 1

Esto se puede simplificar usando la cláusula Partition By. En lo anterior, si una cuenta tiene tres usuarios, entonces la subconsulta los numera 1,2 y 3, y para una clave de cuenta diferente, restablecerá la numeración. Esto significa que para cada clave de cuenta única, siempre habrá un 1, y potencialmente 2,3,4, etc.

Por lo tanto, se filtra en Ranking=1 para agarrar el primero de cada grupo.

Esto le dará una fila por cuenta, y si hay al menos un usuario para esa cuenta, entonces te dará el usuario con la clave más baja (porque uso un join izquierdo, siempre obtendrás una lista de cuentas incluso si no existe ningún usuario). Reemplace Order By u.UserKey con otro campo si prefiere que el primer usuario sea elegido alfabéticamente o con algún otro criterio.

La respuesta material de Dominic Goulet es ingeniosa. Pero, si su DATE_FIELD es SMALLDATETIME (en lugar de DATETIME), entonces la longitud ISO 8601 será 19 en lugar de 23 (porque SMALLDATETIME no tiene milisegundos) - así que ajuste el parámetro STUFF en consecuencia o el valor devuelto de la función STUFF será incorrecto (faltarán los primeros cuatro caracteres).

First y Last no existen en Sql Server 2005 o 2008, pero en Sql Server 2012 hay una función First_Value, Last_Value. Intenté implementar el agregado Primero y Último para Sql Server 2005 y llegué al obstáculo de que sql server garantiza el cálculo del agregado en un orden definido. (Ver atributo SqlUserDefinedAggregateAttribute.IsInvariantToOrder Propiedad, que no está implementado.) Esto podría deberse a que el analizador de consultas intenta ejecutar el cálculo de la agregue en varios subprocesos y combine los resultados, lo que acelera la ejecución, pero no garantiza un orden en el que se agregan los elementos.

Puede utilizar OUTER APPLY, consulte la documentación .

SELECT User1.Name, Account.* FROM Account
OUTER APPLY 
    (SELECT  TOP 1 Name 
    FROM [User]
    WHERE Account.ID = [User].Account_ID
    ORDER BY Name ASC) User1

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

He comparado todos los métodos, el método más sencillo y rápido para lograr esto es usando outer/cross apply

SELECT u.Name, Account.* FROM Account
OUTER APPLY (SELECT TOP 1 * FROM User WHERE Account.ID = Account_ID ) as u

CROSS APPLY funciona como INNER JOIN y obtiene las filas donde ambas tablas están relacionadas, mientras que OUTER APPLY funciona como LEFT OUTER JOIN y obtiene todas las filas de la tabla izquierda (Ver aquí)

Hay varias maneras de hacer esto, aquí una rápida y sucia.

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

Define "Primero". Lo que usted piensa como primero es una coincidencia que normalmente tiene que ver con el orden del índice agrupado, pero no se debe confiar en (puede inventar ejemplos que lo rompan).

Tiene razón en no usar MAX() o MIN(). Si bien es tentador, considere el escenario en el que el nombre y el apellido están en campos separados. Podrías obtener nombres de diferentes registros.

Ya que parece que todo lo que realmente te importa es que obtengas exactamente un registro arbitrario para cada grupo, lo que puede hacer es solo MIN o MAX un campo de ID para ese registro, y luego unir la tabla en la consulta sobre ese ID.

(Ligeramente fuera de tema, pero) A menudo corro consultas agregadas para listar resúmenes de excepciones, y luego quiero saber POR qué un cliente está en los resultados, así que use MIN y MAX para dar 2 muestras semialeatorias que puedo ver en detalles, por ejemplo

SELECT Customer.Id, COUNT(*) AS ProblemCount
      , MIN(Invoice.Id) AS MinInv, MAX(Invoice.Id) AS MaxInv
FROM Customer
INNER JOIN Invoice on Invoice.CustomerId = Customer.Id
WHERE Invoice.SomethingHasGoneWrong=1
GROUP BY Customer.Id

Crear y unirse con una subselección 'FirstUser' que devuelve el primer usuario para cada cuenta

SELECT User.Name, Account.* 
FROM Account, User, 
 (select min(user.id) id,account_id from User group by user.account_id) as firstUser
WHERE Account.ID = User.Account_ID 
 and User.id = firstUser.id and Account.ID = firstUser.account_id