retrive the highest number and datetime

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

Transact-SQL (2000)

retrive the highest number and datetime

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-13 : 22:12:33

[code]
ID Atime ACount Btime BCount
1 2004-05-29 08:00:00.000 8 2004-05-29 08:00:00.000 2
2 2004-04-27 14:00:00.000 6 2004-07-04 13:00:00.000 5
3 2004-06-07 19:00:00.000 3 2004-07-05 21:00:00.000 1

[/code]
I have a following table which has the date time and the number of logins. I would like to create a SQL script that return Atime and Btime witch has the highest count number.

so, the result should looks like:
[code]
Atime Acount Btime Bcount
2004-05-29 08:00:00.000 8 2004-07-04 13:00:00.000 5

[/code]

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-13 : 23:16:11

SELECT (SELECT TOP 1 Atime FROM table WHERE ACount (SELECT MAX(ACount) FROM table)) as Atime,
(SELECT MAX(ACount) FROM table) as ACount,
(SELECT TOP 1 Btime FROM table WHERE BCount IN (SELECT MAX(BCount) FROM table)) as Btime,
(SELECT MAX(BCount) FROM table) as BCount

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-13 : 23:30:41

thanks, but I am getting syntax errors

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'as'.
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'as'.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-13 : 23:47:01

oops....

SELECT (SELECT TOP 1 Atime FROM xxx WHERE ACount IN (SELECT MAX(ACount) FROM xxx)) as Atime,
(SELECT MAX(ACount) FROM xxx) as ACount,
(SELECT TOP 1 Btime FROM xxx WHERE BCount IN (SELECT MAX(BCount) FROM xxx)) as Btime,
(SELECT MAX(BCount) FROM xxx) as BCount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources