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.
| Author |
Topic |
|
sunnysak
Starting Member
7 Posts |
 Posted - 2005-04-19 : 00:06:05
|
| Guys, here is what I've in a table say an employee_log FTS_A1 1/23/2004 10:42:31 AM FTS_A1 9/9/2004 3:27:29 PM FTS_A1 3/26/2004 9:53:56 PM FTS_A1 3/26/2004 5:47:59 PM FTS_A1 4/16/2004 9:49:07 AM FTS_A111 8/16/2004 7:28:39 AM FTS_A111 7/13/2004 12:06:16 PM FTS_A111 5/11/2004 9:27:55 AMI want to write a select statement that gives me only 1 most recent record based on the date. like FTS_A1 9/9/2004 3:27:29 PM FTS_A111 8/16/2004 7:28:39 AM Etc. Etc.. any idea? actually it sounds simple, but when I put it in those groupby IN Exisits.. notin is workin for me :(Thanks in advance |
|
|
sunnysak
Starting Member
7 Posts |
Posted - 2005-04-19 : 00:22:46
|
| Never mind guys, with help of an other fellow buddy, came up with this and it works fine :)SELECT * FROM employee_log WHERE (EntryDate = (SELECT MAX(EntryDate)FROM employee_log b WHERE a.UserID = b.UserID)) |
 |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-04-19 : 08:48:34
|
This should also work, but not return any duplicates. Yours will return dupes if the datetime field happens to match (yes, it can happen).SELECT <column list>, MAX(EntryDate) as EntryDate FROM employee_logGROUP BY UserIDEDIT whoops  |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-04-19 : 08:55:00
|
| >>SELECT <column list>, MAX(EntryDate) as EntryDate >>FROM employee_log>>GROUP BY UserIDThat works as long as <column list> is only UserID or other aggregate functions.- Jeff |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-04-19 : 09:14:50
|
| hmmm forgot about that. :/ |
|
|
|
|
|
|
SELECT * with Group question