GETTING THE LATEST 2 RECORDS FOR A CATEORY

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  December 3, 2015 at 12:09 pm

  #322726

  PROBLEM:

  Table has multiple dated records for each category, I need to extract the 2 most recent records by category.

  SAMPLE DATA:

  CREATE TABLE CATEGORYHISTORY(

  CAT_ID VARCHAR(5),

  STARTDATE DATE,

  ENDDATE DATE,

  REASON CHAR(1))

  INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('ABCDE', '2015-01-01', '2015-05-31', 'X')

  INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('ABCDE', '2015-06-01', '2015-12-31', 'C')

  INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('ABCDE', '2016-01-01', NULL, 'B')

  INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('FGHIJ', '2015-01-01', '2015-04-30', 'X')

  INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('FGHIJ', '2015-05-01', '2015-12-31', 'C')

  INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('FGHIJ', '2016-01-01', NULL, 'B')

  RESULTS TO BE FOUND:

  CAT_ID STARTDATE ENDDATE REASON

  ABCDE 2015-06-01 2015012-31 C

  ABCDE 2016-01-01 NULL B

  FGHIJ 2015-05-01 2015-12-31 C

  FGHIJ 2016-01-01 NULL B

  Is there a simple TSQL answer to this?

• Sean Lange

  SSC Guru

  Points: 286562

  More actions

  December 3, 2015 at 12:45 pm

  #1843873

  Nilssond (12/3/2015)

  ---

  PROBLEM:

  Table has multiple dated records for each category, I need to extract the 2 most recent records by category.

  SAMPLE DATA:

  CREATE TABLE CATEGORYHISTORY(

  CAT_ID VARCHAR(5),

  STARTDATE DATE,

  ENDDATE DATE,

  REASON CHAR(1))

  INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('ABCDE', '2015-01-01', '2015-05-31', 'X')

  INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('ABCDE', '2015-06-01', '2015-12-31', 'C')

  INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('ABCDE', '2016-01-01', NULL, 'B')

  INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('FGHIJ', '2015-01-01', '2015-04-30', 'X')

  INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('FGHIJ', '2015-05-01', '2015-12-31', 'C')

  INSERT INTO CATEGORYHISTORY (CAT_ID, STARTDATE, ENDDATE, REASON) VALUES('FGHIJ', '2016-01-01', NULL, 'B')

  RESULTS TO BE FOUND:

  CAT_ID STARTDATE ENDDATE REASON

  ABCDE 2015-06-01 2015012-31 C

  ABCDE 2016-01-01 NULL B

  FGHIJ 2015-05-01 2015-12-31 C

  FGHIJ 2016-01-01 NULL B

  Is there a simple TSQL answer to this?

  Excellent job posting ddl and sample data. Makes this a lot easier. You can do this quite easily with ROW_NUMBER.

  with MyCte as

  (

  select *

  , ROW_NUMBER() over(partition by CAT_ID order by StartDate desc) as RowNum

  from CATEGORYHISTORY

  )

  select *

  from MyCte

  where RowNum <= 2

  order by CAT_ID

  , StartDate

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/