SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 MSSQL Query Question - Please Help!
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

hawkswim32
Starting Member

1 Posts

Posted - 09/14/2006 :  12:03:02  Show Profile
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
Flowing Fount of Yak Knowledge

2144 Posts

Posted - 09/14/2006 :  12:24:53  Show Profile
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

Edited by - DonAtWork on 09/14/2006 12:36:02
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 09/15/2006 :  04:05:39  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 09/15/2006 :  10:24:13  Show Profile  Send madhivanan a Yahoo! Message
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
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2144 Posts

Posted - 09/15/2006 :  14:02:40  Show Profile

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


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
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2144 Posts

Posted - 09/15/2006 :  14:02:47  Show Profile
stoopid double post

Edited by - DonAtWork on 09/15/2006 14:03:13
Go to Top of Page

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 09/15/2006 :  17:01:41  Show Profile
But Performance wise Harsh query works better I think

Thanks,
Gopi Nath Muluka
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000