How to Select Maximum Datestamp Record

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 2005 Forums

Transact-SQL (2005)

How to Select Maximum Datestamp Record

Author

Topic

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-09-21 : 05:39:59

Hi All,
This is the Data.

Number TheNumber Type Datestamp
IM10134 001A10413 Open 2010-07-19 07:51:43.000
IM10134 001A10424 Status Change 2010-08-02 09:45:27.000
IM10134 001A10425 External Vendor Assignment 2010-08-02 09:45:27.000
IM10134 001A10426 Update 2010-08-02 09:47:49.000

Query is

Select Number,TheNumber,Type,Max(Datestamp) From ACTIVITYM1
Where Number = 'IM10134'
Group By Number,TheNumber,Type

So How to Select Maximum datestamp Record only.Kindly help me.

V.NAGARAJAN

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-09-21 : 05:42:15

Do u need this?

Select Max(Datestamp) From ACTIVITYM1
Where Number = 'IM10134'

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-09-21 : 05:44:38

Hi, I know this query.I want remaining fields also.so how to select.

quote:
Originally posted by senthil_nagore

Do u need this?

Select Max(Datestamp) From ACTIVITYM1
Where Number = 'IM10134'

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

V.NAGARAJAN

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-09-21 : 05:53:12

As shown in your example the TheNumber,Type column are always changing!

What your expected output?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-21 : 06:02:14

Is it not just this.......


SELECT TOP 1
     Number
    , TheNumber
    , Type
    , Datestamp
FROM
    ACTIVITYM1
WHERE
    Number = 'IM10134'
ORDER BY
    Datestamp DESC

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-09-21 : 06:05:45

it OK for selecting single record only.how to select multiple record in a Table.How it is possible.

quote:
Originally posted by Transact Charlie

Is it not just this.......

SELECT TOP 1
Number
, TheNumber
, Type
, Datestamp
FROM
ACTIVITYM1
WHERE
Number = 'IM10134'
ORDER BY
Datestamp DESC

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

V.NAGARAJAN

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-21 : 06:09:59

I don't understand what you are asking for.

Given the data you posted:


Number	TheNumber Type	 Datestamp
IM10134	001A10413 Open	 2010-07-19 07:51:43.000
IM10134	001A10424 Status Change	 2010-08-02 09:45:27.000
IM10134	001A10425 External Vendor Assignment 2010-08-02 09:45:27.000
IM10134	001A10426 Update	 2010-08-02 09:47:49.000

My query produces the line you said you wanted. (the one with the highest datestamp.

What do you actually want? Can you show us a dataset and the required output?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-09-21 : 06:20:24

This is the data

Number Number Type Datestamp
IM10134 001A10413 Open 2010-07-19 07:51:43.000
IM10134 001A10424 Status Change 2010-08-02 09:45:27.000
IM10134 001A10425 External Vendor 2010-08-02 09:45:27.000
IM10134 001A10426 Update 2010-08-02 09:47:49.000
IM10135 001A10414 Open 2010-07-20 09:00:51.000
IM10136 001A10415 Open 2010-07-20 09:28:04.000
IM10136 001A10429 Resolved 2010-08-02 11:25:19.000
IM10137 001A10416 Open 2010-07-20 09:38:23.000
IM10138 001A10417 Open 2010-07-20 09:44:12.000
IM10138 001A10427 Resolved 2010-08-02 11:20:56.000
IM10138 001A10428 Closed 2010-08-02 11:21:48.000
IM10139 001A10418 Open 2010-07-22 15:18:19.000

I want the folloowing result set.

Number TheNumber Type Datestamp
IM10134 001A10426 Update 2010-08-02 09:47:49.000
IM10135 001A10414 Open 2010-07-20 09:00:51.000
IM10136 001A10429 Resolved 2010-08-02 11:25:19.000
IM10137 001A10416 Open 2010-07-20 09:38:23.000
IM10138 001A10428 Closed 2010-08-02 11:21:48.000
IM10139 001A10418 Open 2010-07-22 15:18:19.000

How make a query

quote:
Originally posted by Transact Charlie

I don't understand what you are asking for.

Given the data you posted:

Number TheNumber Type Datestamp
IM10134 001A10413 Open 2010-07-19 07:51:43.000
IM10134 001A10424 Status Change 2010-08-02 09:45:27.000
IM10134 001A10425 External Vendor Assignment 2010-08-02 09:45:27.000
IM10134 001A10426 Update 2010-08-02 09:47:49.000

My query produces the line you said you wanted. (the one with the highest datestamp.

What do you actually want? Can you show us a dataset and the required output?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

V.NAGARAJAN

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-09-21 : 06:39:55

Try this

Select * from (
select row_number() over(partition by Number order by Datestamp desc)
as s_no ,* from table_name) a where s_no=1

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-21 : 07:08:01

Or if you don't want the ranking columns



SELECT
	dt.[Number]
	, dt.[TheNumber]
	, dt.[Type]
	, dt.[Datestamp]
FROM
	(
	SELECT
		ROW_NUMBER() OVER (
			PARTITION BY [Number]
			ORDER BY [Datestamp]
			)
			AS [rowPos]
		, [Number] AS [Number]
		, [TheNumber] AS [TheNumber]
		, [Type] AS [Type]
		, [Datestamp] AS [Datestamp]
	FROM
		<<TABLE_NAME>>
	)
	AS dt
WHERE
	dt.[rowPos] = 1

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources