How to get first row of data from a set of data in sql server

Question

I am saving all activities by customers on their orders in a history table in a sql server. For example when they place an order it is saved. When they edit the same order, a new row is inserted into the history table and so on. I want to create a report displaying the data. The sql query should get all the first-row corresponding to the order id, 'order by orderDate desc'. We can't use the Type in the query, as 'New Order' might be missing for some sets of data as the table is populated from a variety of data sources. The data will be displayed in a grid. When the user clicks on the rows in the grid, it should get the rest of the data for that orderID as child data. For example, in the screenshot shown below, the query should get SeqNo 1 and 4 as highlighted in yellow. I am new to sql. Any idea how to write the query? Note: I know how to write the query for the child data

Answer 1

We can use TOP along with ROW_NUMBER here:

SELECT TOP 1 WITH TIES
    SeqNo, OrderID, CustomerID, ProductID, Quantity, Amount, OrderDate, Type
FROM yourTable
ORDER BY ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderDate DESC);