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

CAMPzxzxDeathzxzxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.