Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 18th, 2004, 10:24 AM
Registered User
 
Join Date: Aug 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Trouble developing query

I have an app in which values of various factors are stored in a table:

Date/Time Factor Value
01/01/2001 10:10.32 F21 2
01/01/2001 11:24.32 F21 1
01/01/2001 11:25.39 Q4 23
01/01/2001 11:29.12 Q4 65
01/01/2002 05:32.54 F21 3

I need to develop a view/sp to retrieve a recordset with only the most recent values for each unique factor. I am familiar with how to do this with access (GroupBy - First functionality) but I have not been able to figure this out in SQL Server 2000.

From the above sample table, I am looking for the following result:

Date/Time Factor Value
01/01/2001 11:29.12 Q4 65
01/01/2002 05:32.54 F21 3

Any assistance would be appreciated!

 
Old August 18th, 2004, 01:28 PM
Authorized User
 
Join Date: Jul 2004
Posts: 66
Thanks: 0
Thanked 0 Times in 0 Posts
Default


SELECT DT,FACTOR,VALUE FROM
(SELECT MAX(DATETIMEFIELD) AS DT,FACTOR FROM MYTABLE GROUP BY FACTOR) A
INNER JOIN MYTABLE B ON A.DT=B.DATETIMEFIELD AND A.FACTOR=B.FACTOR

Let me know how it works.
David
 
Old August 18th, 2004, 01:32 PM
Registered User
 
Join Date: Aug 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

dhay1999

Thanks for the response, your suggestion worked like a charm!

I knew the solution would be straight forward... I guess I was staring at my screen too long to see the obvious!!

Thanks again!






Similar Threads
Thread Thread Starter Forum Replies Last Post
developing on a server blitzkriegbop General .NET 1 January 4th, 2005 04:10 PM
Wildcard characters in query giving trouble programmer_kay ADO.NET 3 March 21st, 2004 10:04 PM
Nested Query Trouble ashley_y ADO.NET 2 December 23rd, 2003 01:32 PM
Nested Query Trouble ashley_y VB How-To 1 December 23rd, 2003 05:10 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.