Records Sort By Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Records Sort By Date

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2012-04-19 : 11:59:06

Hi Guys,

I need a bit of help in creating a VIEW which would display the following data:


ID          FromDate                       DateLastUpdate                 HoldingTank
----------- ------------------------------ ------------------------------ ---------------
321937      28/11/2001                     28/11/2001                     D10            
321937      18/03/2002                     18/03/2002                     F2             
321937      17/02/2003                     17/02/2003                     UKN            
321937      17/02/2003                     18/02/2011                     G8

like this:


ID          FromDate                       DateLastUpdate                 HoldingTank
----------- ------------------------------ ------------------------------ ---------------
321937      28/11/2001                     28/11/2001                     D10         
321937      18/03/2002                     18/03/2002                     F2
321937      17/02/2003                     18/02/2011                     G8

Basically if there are two same FromDate then only pick the latest one based on DateLastUpdate

umertahir
Posting Yak Master

154 Posts

Posted - 2012-04-19 : 12:08:41

In order to create the example table the following query can be helpful to test:


CREATE TABLE [dbo].[tblSorter](
	[ID] [int] NOT NULL,
	[FromDate] [varchar](30) NULL,
	[DateLastUpdate] [varchar](30) NULL,
	[HoldingTank] [char](15) NULL
) ON [PRIMARY]



INSERT INTO [dbo].[tblSorter]
           ([ID]
           ,[FromDate]
           ,[DateLastUpdate]
           ,[HoldingTank])
     VALUES
           (321937
           ,'28/11/2001'
           ,'28/11/2001'
           ,'D10')
           
INSERT INTO [dbo].[tblSorter]
           ([ID]
           ,[FromDate]
           ,[DateLastUpdate]
           ,[HoldingTank])
     VALUES
           (321937
           ,'18/03/2002'
           ,'18/03/2002'
           ,'F2')


INSERT INTO [dbo].[tblSorter]
           ([ID]
           ,[FromDate]
           ,[DateLastUpdate]
           ,[HoldingTank])
     VALUES
           (321937
           ,'17/02/2003'
           ,'17/02/2003'
           ,'UKN')   


INSERT INTO [dbo].[tblSorter]
           ([ID]
           ,[FromDate]
           ,[DateLastUpdate]
           ,[HoldingTank])
     VALUES
           (321937
           ,'17/02/2003'
           ,'18/02/2011'
           ,'G8')

umertahir
Posting Yak Master

154 Posts

Posted - 2012-04-19 : 12:18:33

I was doing something like this, but that only brings out one record rather than three:


SET DATEFORMAT DMY
SELECT		*
FROM		tblSorter AS a
WHERE		DateLastUpdate = (SELECT MAX(DateLastUpdate) FROM tblSorter WHERE ID = a.ID)
ORDER BY	CAST(FromDate AS DATETIME), CAST(DateLastUpdate AS DATETIME)

Result:
ID          FromDate                       DateLastUpdate                 HoldingTank
----------- ------------------------------ ------------------------------ ---------------
321937      28/11/2001                     28/11/2001                     D10

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-19 : 12:21:12

Here is one way:

SELECT *
FROM
	(
		SELECT 
			*,
			ROW_NUMBER() OVER (PARTITION BY ID, FromDate ORDER BY DateLastUpdate DESC) AS RowNum
		FROM 
			dbo.tblSorter
	) AS T
WHERE
	RowNum = 1

umertahir
Posting Yak Master

154 Posts

Posted - 2012-04-20 : 04:24:25

Thanks for your reply Lamprey.

I found a bit simpler way to do it is like:


SELECT		*
FROM		tblSorter AS a
WHERE		DateLastUpdate IN (SELECT MAX(DateLastUpdate) FROM tblSorter WHERE ID = a.ID GROUP BY ID, FromDate)


Result:
ID          FromDate                       DateLastUpdate                 HoldingTank
----------- ------------------------------ ------------------------------ ---------------
321937      28/11/2001                     28/11/2001                     D10            
321937      18/03/2002                     18/03/2002                     F2             
321937      17/02/2003                     18/02/2011                     G8

quote:

Originally posted by Lamprey

Here is one way:

SELECT *
FROM
	(
		SELECT 
			*,
			ROW_NUMBER() OVER (PARTITION BY ID, FromDate ORDER BY DateLastUpdate DESC) AS RowNum
		FROM 
			dbo.tblSorter
	) AS T
WHERE
	RowNum = 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-20 : 06:04:37

quote:
Originally posted by umertahir

Thanks for your reply Lamprey.

I found a bit simpler way to do it is like:

Is "simpler" equal to faster?

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources