query Help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

query Help

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2008-11-21 : 16:11:05

I have a table which looks like this

Acct.No Type DateTime User
FN10000204149 P 11/20/08 10:20 GUPRA
FN10000204229 N 11/20/08 10:30 GUSTV
FN10000204229 N 11/20/08 10:40 CALPER
FN10000204230 N 11/20/08 10:30 GUSTV
FN10000204230 N 11/20/08 10:40 CALPER
FN10000204230 N 11/20/08 10:45 GUSTV
FN10000204230 P 11/20/08 10:50 EDPA

the query should look for the MAX(Time) and who the user was and print the result as below.

Acct.No Type User
FN10000204229 N 11/20/08 10:40 CALPER
FN10000204230 P 11/20/08 10:50 EDPA

Please provide any suggestions or clues.

Thanks.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-21 : 17:10:22

Is this any good?

declare @sample table (FileNumber varchar(50), flag char(1), myDate datetime, username varchar(50))

insert @sample
select 'FN10000204149', 'P', '11/20/08 10:20', 'GUPRA'
union all
select 'FN10000204229', 'N', '11/20/08 10:30', 'GUSTV'
union all
select 'FN10000204229', 'N', '11/20/08 10:40', 'CALPER'
union all
select 'FN10000204230', 'N', '11/20/08 10:30', 'GUSTV'
union all
select 'FN10000204230', 'N', '11/20/08 10:40', 'CALPER'
union all
select 'FN10000204230', 'N', '11/20/08 10:45', 'GUSTV'
union all
select 'FN10000204230', 'P', '11/20/08 10:50', 'EDPA'

select
top 1 FileNumber, flag, max(myDate) myDate, username
from
@sample
group by
Filenumber, flag, username
order by
myDate desc

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-21 : 17:11:15

The output is:
FN10000204230 P 2008-11-20 10:50:00.000 EDPA

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-21 : 17:25:00

Your expected output is confusing me.. DO you mean the MAX Time by Account Number? So you should get 3 rows?

SELECT 
	S.*
FROM
	@Sample AS S
INNER JOIN
	(
		SELECT FileNumber, MAX(MyDate) AS MyDate
		FROM @Sample
		GROUP BY FileNumber
	) AS T
	ON S.FileNumber = T.FileNumber
	AND S.MyDate = T.MyDate

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-21 : 17:51:07

May be this: But Lamprey query should do it if no condition:
SELECT
S.*
FROM
@Sample AS S
INNER JOIN
(
SELECT FileNumber, MAX(MyDate) AS MyDate
FROM @Sample
GROUP BY FileNumber
) AS T
ON S.FileNumber = T.FileNumber
AND S.MyDate = T.MyDate
Where s.FileNumber <> 'FN10000204149'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 00:36:32

[code]SELECT Acct.No, Type, DateTime, User
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Acct.No ORDER BY DateTime DESC) AS Seq,
COUNT(*) OVER (PARTITION BY Acct.No) AS RecCnt,
Acct.No, Type, DateTime, User
FROM Table
)t
WHERE t.Seq =1
AND t.RecCnt>1
[/code]

as per your sample output

and just in case you forgot to add first record, then it should be this

[code]SELECT Acct.No, Type, DateTime, User
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Acct.No ORDER BY DateTime DESC) AS Seq,
Acct.No, Type, DateTime, User
FROM Table
)t
WHERE t.Seq =1
[/code]

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2008-11-25 : 11:42:54

Thanks Everyone for all the suggestions.
in the result set i need only one record for a given account and it should be the max(DateTime) of that associated record.

for example FN10000204229 N 11/20/08 10:30 GUSTV
FN10000204229 N 11/20/08 10:40 CALPER

from the above two rows for a given acct no in a table i need only
FN10000204229 N 11/20/08 10:40 CALPER (there could be any numbers of rows for different times and users).

Thanks once again for everyone.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 11:47:31

didnt the suggestions provide you reqd solution?

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2008-11-25 : 14:25:09

Hi visakh; I had to work on both the 2000 and 2005 servers and Row_Number was not working in 2000 so i was looking for more help. I tweaked a bit of lamprey's code and it worked for me.

Once again thanks everyone for the valuable suggestions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 00:52:34

quote:
Originally posted by sreenu9f

Hi visakh; I had to work on both the 2000 and 2005 servers and Row_Number was not working in 2000 so i was looking for more help. I tweaked a bit of lamprey's code and it worked for me.

Once again thanks everyone for the valuable suggestions.

Ok no problem
In that case, please post your future questions in relevant forums. This is 2005 forum. you should have posted this in 2000 forum to get 2000 specific soln.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources