Top 1 Records in Each Group

• murthysundara

  SSC-Addicted

  Points: 490

  More actions

  July 23, 2011 at 4:27 am

  #244838

  Table Structure

  CREATE TABLE [dbo].[sts_stock_reg](

  [Sys_SR_Num] [numeric](20, 0) IDENTITY(1,1) NOT NULL,

  [Sys_Item_Type_Num] [numeric](15, 0) NOT NULL,

  [Sys_Item_Num] [numeric](15, 0) NOT NULL,

  [Trans_date] [datetime] NOT NULL,

  [Rcpt_Qty] [numeric](20, 4) NULL,

  [Sply_Date] [datetime] NULL,

  [Sply_Qty] [numeric](20, 4) NULL,

  [Open_blnce] [numeric](20, 4) NULL,

  [Trans_Blnce_Qty] [numeric](20, 4) NULL,

  [Trans_Open_Blnce] [numeric](20, 4) NULL

  CONSTRAINT [PK_sts_stock_reg_1] PRIMARY KEY CLUSTERED

  (

  [Sys_SR_Num] ASC

  )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  SET ANSI_PADDING OFF

  Data in Table

  Sys_SR_Num Sys_Item_Type_Num Sys_Item_Num Trans_date Rcpt_Qty Sply_Date Sply_Qty Open_blnce Trans_Blnce_Qty Trans_Open_Blnce

  1 1 1050 01-04-2011 08:00 AM 100 null 0 null 100 0

  2 1 1050 01-04-2011 09:00 AM 500 null 0 null 600 100

  3 1 1050 01-04-2011 10:00 AM 500 null 0 null 1100 600

  4 1 1051 01-04-2011 10:00 AM 500 null 0 null 500 0

  5 1 1051 01-04-2011 11:00 AM 600 null 0 null 1100 500

  i have to pickup the top records on desc order

  results is

  3 1 1050 01-04-2011 10:00 AM 500 null 0 null 1100 600

  5 1 1051 01-04-2011 11:00 AM 600 null 0 null 1100 500

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  July 23, 2011 at 11:39 am

  #1357435

  Is this the result you are looking for:

  ;WITH cte

  AS (SELECT Row_Number() OVER(PARTITION BY Sys_Item_Num ORDER BY Trans_date DESC) as rn,

  Sys_SR_Num, Sys_Item_Type_Num, Sys_Item_Num, Trans_date, Rcpt_Qty, Sply_Date, Sply_Qty, Open_blnce,

  Trans_Blnce_Qty, Trans_Open_Blnce FROM [dbo].[sts_stock_reg])

  SELECT * FROM cte WHERE rn = 1

  Results:

  13110502011-01-04 10:00:00.000500.0000NULL0.0000NULL1100.0000600.0000

  15110512011-01-04 11:00:00.000600.0000NULL0.0000NULL1100.0000500.0000

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]