• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 12
  • Last Modified:

Need help writing Max(DateTime) partition SP

BEGIN    
SELECT		

			[LiveCoinOrderID],
			[Symbol],
			[Quantity],		 
			[TimeStamp] AS UPDATED
			FROM
(
     SELECT 
			[LiveCoinOrderID],
			[Symbol],
			[Quantity],			
            Max([TimeStamp]) over(partition by [TimeStamp] order by UPDATED desc) as MaxTimeStamp, 
            Row_Number() over(partition by [TimeStamp] order by UPDATED desc) as RowNumber
     FROM [dbo].[LiveCoinOrder]
	 WHERE [DBStatus] = 'New'
) 

			WHERE RowNumber = 1
END

Open in new window

0
CAMPzxzxDeathzxzx
Asked:
CAMPzxzxDeathzxzx
  • 4
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
No clue what your question is.  Please re-ask it in basic English in the form of a question as opposed to a 'Need Help' statement and then a T-SQL query.
0
 
CAMPzxzxDeathzxzxAuthor Commented:
I don't understand the remark because you wrote it in basic English.  But here I'll rewrite the question in my "I'm a developer under a wicked deadline trying to get things done in the least amount of time as possible" English.

This SP I mocked up is a close approximation to what I need.  It doesn't pass the IntelliSense (intelligent code completion) smell test so I know it is written poorly as provided.  I was hoping that someone would happen along and fix it for me.

Thanks in advance,
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
< guessing >

Since the 'order by UPDATED desc' is good, then you should be able to alias that and then get the WHERE alias = 1 in the main query.  Something like..

  
SELECT LiveCoinOrderID, Symbol, Quantity, TimeStamp AS UPDATED
FROM (
   SELECT 
      LiveCoinOrderID,
      Symbol,
      Quantity,   
      Timestamp,       
      ROW_NUMBER() over(partition by TimeStamp order by UPDATED desc) as MaxTimeStamp, 
      ROW_NUMBER() over(partition by TimeStamp order by UPDATED desc) as RowNumber
   FROM dbo.LiveCoinOrder
   WHERE DBStatus = 'New')  a
WHERE MaxTimeStamp = 1  -- or RowNumber = 1

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
CAMPzxzxDeathzxzxAuthor Commented:
I've been playing with this for 6 ways to Sunday and I can't get the results I need.  I need this to return the first row //as in [TimeStamp] desc


IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[usp_GetNewLiveCoinOrder]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_GetNewLiveCoinOrder];
GO
CREATE PROCEDURE [dbo].[usp_GetNewLiveCoinOrder]
AS
BEGIN    
SELECT [LiveCoinOrderID], [Symbol], [Quantity], [TimeStamp]
FROM (
   SELECT 
      [LiveCoinOrderID],
      [Symbol],
      [Quantity],   
      [Timestamp],       
      ROW_NUMBER() over(partition by [TimeStamp] order by [TimeStamp] desc) as MaxTimeStamp      
   FROM dbo.LiveCoinOrder
   WHERE DBStatus = 'New') a   
END
GO

Open in new window


results:

The results..
0
 
CAMPzxzxDeathzxzxAuthor Commented:
>>>>SELECT Top 1 = Duh

Here's my new challenge; Can this be done better as in faster and more elegant?
0
 
CAMPzxzxDeathzxzxAuthor Commented:
In proper English - Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now