selecting Max 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

General SQL Server Forums

New to SQL Server Programming

selecting Max Date

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-06 : 07:35:21

Hey guys
Hope your well
This is a really basic question, but i always struggle with this kind of question
Aim – Get the latest [Submit_Date] for each indvidual fdmsaccountno
The table is
SELECT TOP 1000 [FDMSAccountNo]
,[Submit_Date]
,[Net_Trans]
,[Net_Sales]
FROM [FDMS].[dbo].[stg_Daily_Sales]
where FDMSAccountNo = '878020121884'
order by Submit_Date desc

Currently the results provide
Data from 05/08/2013 to 01/08/2013.
result required would be 05/08/2013

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-06 : 07:43:45

The question is not 100% clear to me, but I am guessing this is what you are looking for

SELECT * FROM 
(
	SELECT [FDMSAccountNo]
	,[Submit_Date]
	,[Net_Trans]
	,[Net_Sales]
	,ROW_NUMBER() OVER (PARTITION BY [FDMSAccountNo] ORDER BY Submit_Date DESC) AS N
	FROM [FDMS].[dbo].[stg_Daily_Sales]
) s WHERE N=1
order by Submit_Date desc

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-06 : 07:46:40

james K

That is it indeed,

Its an over partition, need to learn how to use that more sucesfully

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-06 : 07:48:06

[code]Method1:
SELECT DISTINCT [FDMSAccountNo]
,[Submit_Date]
,[Net_Trans]
,[Net_Sales]
,MAX(Submit_Date) OVER (PARTITION BY [FDMSAccountNo]) AS N
FROM [FDMS].[dbo].[stg_Daily_Sales]

Method2:
SELECT [FDMSAccountNo]
,MAX([Submit_Date]) LatestSubmitDate
FROM [FDMS].[dbo].[stg_Daily_Sales]
GROUP BY [FDMSAccountNo][/code]

--
Chandu

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-06 : 07:51:26

Using partition by clause is fairly simple. The key is what you stated in your original question:

"Get the latest [Submit_Date] for each indvidual fdmsaccountno"

The part in green "each fdmsaccountno" tells you that you need to partition by fdmsaccountno
The part in red "latest Submit_Date" tells you that you need to order by Submit_Date. Order by descending because you want to get the latest submit_date first.

Fabiano Amorim's article here is pretty good: https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-06 : 07:52:52

quote:

Originally posted by bandi

Method1: 
SELECT DISTINCT [FDMSAccountNo]
 ,[Submit_Date]
 ,[Net_Trans]
 ,[Net_Sales]
 ,MAX(Submit_Date) OVER (PARTITION BY [FDMSAccountNo]) AS N
FROM [FDMS].[dbo].[stg_Daily_Sales]

Method2: 
SELECT [FDMSAccountNo]
 ,MAX([Submit_Date]) LatestSubmitDate
FROM [FDMS].[dbo].[stg_Daily_Sales]
GROUP BY [FDMSAccountNo]

--
Chandu

Welcome back Chandu! Haven't seen you around for quite a while.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-06 : 08:23:26

quote:
Originally posted by James K

quote:
Originally posted by bandi

Method1:
SELECT DISTINCT [FDMSAccountNo]
,[Submit_Date]
,[Net_Trans]
,[Net_Sales]
,MAX(Submit_Date) OVER (PARTITION BY [FDMSAccountNo]) AS N
FROM [FDMS].[dbo].[stg_Daily_Sales]

Method2:
SELECT [FDMSAccountNo]
,MAX([Submit_Date]) LatestSubmitDate
FROM [FDMS].[dbo].[stg_Daily_Sales]
GROUP BY [FDMSAccountNo]

--
Chandu
Welcome back Chandu! Haven't seen you around for quite a while.

Thanks James...

--
Chandu

AAKG GROUP
Starting Member

3 Posts

Posted - 2013-08-06 : 17:21:37

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources