CODEWITHSUNDEEP
sqlsql-servert-sql
RUC
RUC

Reputation: 53

Last record and first record in separate columns

I have table as below and need to pull first record in column 2 and last record in column 1&3 based on createDate(column1)

Please help with query 

column1     column2  column3
CreateDate  Amount   Description 
04/02/2020  36000    First meeting 
04/30/2020  9000     CloseWon

Output I need 

column1     column2  column3
CreateDate  Amount   Description 
04/30/2020  36000     CloseWon

Upvotes: 0

Views: 116

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

All supported versions of SQL Server support FIRST_VALUE and LAST_VALUE window functions - so it can be as easy as this:

SELECT DISTINCT FIRST_VALUE(CreateDate) OVER(ORDER BY CreateDate DESC) As CreateDate,
       FIRST_VALUE(Amount) OVER(ORDER BY CreateDate) As Amount,
       FIRST_VALUE(Description) OVER(ORDER BY CreateDate DESC) As Description 
FROM TableName

Upvotes: 1

gotqn
gotqn

Reputation: 43626

You can try this:

DECLARE @DataSource TABLE
(
    [CreateDate] DATE
   ,[Amount] INT
   ,[Description] VARCHAR(18)
);

INSERT INTO @DataSource ([CreateDate], [Amount], [Description])
VALUES ('04/02/2020', 36000, 'First meeting')
      ,('04/30/2020', 9000, 'CloseWon');

WITH DataSource AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY [CreateDate] ASC) AS [FirstRow]
          ,ROW_NUMBER() OVER (ORDER BY [CreateDate] DESC) AS [LastRow]
          ,*
    FROM @DataSource
)
SELECT DS2.[CreateDate]
      ,DS1.[Amount]
      ,DS2.[Description]
FROM DataSource DS1
INNER JOIN DataSource DS2
    ON DS1.[FirstRow] = DS2.[LastRow]
WHERE DS1.[FirstRow] = 1;

The idea is to use ROW_NUMBER two times - to define the first and the last row. Then, simply result of the common table expression to itself.

If you want to group the data - for example you have another column which is going to defined how records are group:

  • add the column(s) to PARTITION BY clause of the ROW_NUMBER
  • when joining the result of the CTE, join by these column(s), too

Upvotes: 0

Related Questions