MSSQL Query Question - Please Help!

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

Old Forums

CLOSED - General SQL Server

MSSQL Query Question - Please Help!

Author

Topic

hawkswim32
Starting Member

1 Post

Posted - 2006-09-14 : 12:03:02

I'm okay with SQL queries, but this one is stumping me.

The table looks like this:

AGENT_ID | MODIFIED | [ 50+ more columns ]
1 | 2004-06-02 11:30:00
1 | 2005-04-02 09:30:00
1 | 2001-03-11 14:33:00
2 | 2004-11-30 12:00:00
2 | 2006-12-12 11:30:00
... ...
37663 | 2004-05-22 12:33:00
37663 | 2006-02-05 09:31:00

What I need to do is get the latest Modified date for each agent. For instance, Agent 1's latest was '2005-04-02 09:30:00' so I want to return that entire row. Continue this on for the rest of the agents until I have a list of latest modification date for each agent.

FYI: There are about 50+ columns in this table.

Any ideas with MSSQL?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-14 : 12:24:53

select Agent_id, max(modified) from YourTable group by Agent_id

EDIT* I ignored the other 50 columns..

EDIT #2 Try this


select * from YourTable  
where Agent_ID + Modified in

(select Agent_id + max(modified) as Foo From  YourTable  group by Agent_id)

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-15 : 04:05:39

Or this:

Select * from Tbl x where modified = (select max(modified) from Tbl y where y.AgentID = x.AgentID)

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-15 : 10:24:13

quote:
Originally posted by DonAtWork

select Agent_id, max(modified) from YourTable group by Agent_id

EDIT* I ignored the other 50 columns..

EDIT #2 Try this

select * from YourTable
where Agent_ID + Modified in

(select Agent_id + max(modified) as Foo From YourTable group by Agent_id)

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

If modified is datetime column, you cant concatenated with int datatype

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-15 : 14:02:40

[code]
create table YourTable (Agent_ID int, Modified datetime, col1 varchar(10), col2 varchar(10))

insert into YourTable (Agent_ID,Modified,Col1,Col2)
select 1 , '2004-06-02 11:30:00','Sedr','foobar' union all select
1 , '2005-04-02 09:30:00','fg','4t34' union all select
1 , '2001-03-11 14:33:00','fae','fa3' union all select
2 , '2004-11-30 12:00:00' , 'dsae','aer3' union all select
2 , '2006-12-12 11:30:00', 'er','er43'

select * from YourTable
where Agent_ID + Modified in

(select Agent_id + max(modified) as Foo From YourTable group by Agent_id)

drop table YourTable
[/code]

It worked. Could be luck. You are right that i should cast them as the same thing then concatenate them.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-15 : 14:02:47

stoopid double post

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 2006-09-15 : 17:01:41

But Performance wise Harsh query works better I think

Thanks,
Gopi Nath Muluka

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources