Get Top 1 Row of Each Group

SQL SELECT TOP 1 FOR EACH GROUP

SELECT TOP 1 WITH TIES 
    [UID]
    ,[RecordStatusID]
    ,[CreatedDate]
    ,[CreatedTime]
    ,[CreatedByID]
    ,[OperationType]
    ,[InventoryLocationID]
    ,[DocumentTypeID]
    ,[DocumentID]
    ,[SOJPersonnelID]
    ,[InventorySerialisedItemID]
    ,[TransactionQty]
    ,[TransactionInventoryStatusID]
    ,[Completed]
    ,[CreatedByType]
    ,[RecordTimeStamp]
FROM 
    [CPData].[dbo].[InventoryAllocations]
ORDER BY
    ROW_NUMBER() OVER(PARTITION BY DocumentID ORDER BY [RecordTimeStamp] DESC);

TOP 1 works with WITH TIES here.

WITH TIES means that when ORDER BY = 1, then SELECT takes this record (because of TOP 1) and all others that have ORDER BY = 1 (because of WITH TIES).

how to get top 1 record from each group

Okay, this is from memory:

;
WITH cte AS (
   SELECT  *
        ,  row_index = row_number() OVER (PARTITION BY companyId, invoiceDate
                                          ORDER BY invoiceID desc )
   FROM    accountingInvoice
)
SELECT     cte.invoiceId,
           cte.CompanyId, 
           cte.invoicedate
FROM       cte
WHERE      cte.row_index = 1

This should give you the last invoice per date per company. If you want a particular date and company, add them as a WHERE clause to the final query.

Larnu's link is also good, although I did this from memory.

BigQuery/SQL: Select first row of each group

SQL Server group by max string column

You can use ROW_NUMBER() to assign a row number based on country and year, ranked by the length of the name descending:

SELECT ID_Country, Year_, Employee_ID, Employee_Name
FROM
(
  SELECT ID_Country, Year_, Employee_ID, Employee_Name, 
    rn = ROW_NUMBER() OVER 
    (
      PARTITION BY ID_Country, Year_ 
      ORDER BY LEN(Employee_Name) DESC, Employee_ID
    )
  FROM dbo.Employee_Tbl
) AS Employees WHERE rn = 1;

• Example db<>fiddle

There is some guessing there about how you'd want to break ties (or maybe you don't care), but there is no concept of "first" unless you state an order explicitly.

Select the top 1 row from each group

You're not specifying how you want ties handled, but this will do it if you want the duplicates displayed;

SELECT a.* FROM MyTable a
LEFT JOIN MyTable b
  ON a.userid=b.userid
 AND CAST(a.version AS INT) < CAST(b.version AS INT)
WHERE b.version IS NULL

An SQLfiddle to test with.

If you want to eliminate duplicates and if they exist pick the newest of them, you'll have to extend the query somewhat;

WITH cte AS (SELECT *, CAST(version AS INT) num_version FROM MyTable)
SELECT a.id, a.userid, a.version, a.datetime 
FROM cte a LEFT JOIN cte b
  ON a.userid=b.userid
 AND (a.num_version < b.num_version OR 
     (a.num_version = b.num_version AND a.[datetime]<b.[datetime]))
WHERE b.version IS NULL

Another SQLfiddle.

select top x by group in ms access

MsAccess can have a subquery as its select expression, which can be used to produce a limited rownumber:

SELECT *
FROM (
SELECT TblA.*, 
   (SELECT count(*) from Sheet1 TblB 
    where tblB.GROUP=tblA.GROUP and tblB.Index<=tblA.Index) as ROWNO
FROM Sheet1 TblA) Step1
where Step1.ROWNO<=Step1.A

Biggest caveat is that if two rows of the same group can have the same Index value.

---

Related Topics