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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-09-14 : 08:19:30
|
| Kenyell Ollie writes "First off, I just want to say that you guys have yet to miss a question asked. With this, here is mine. I am trying to get the records with the most recent dates. Such as:Name: Message: Contact_dt:John Jacobs call him tomorrow 7/12/04John Jacobs he was very nice 7/9/04Max Johnson beware, he's rough 7/12/04Max Johnson he's a monster 7/10/04In this example, I want John Jacobs call him tomorrow 7/12/04Max Johnson beware, he's rough 7/12/04because the dates are more recent but for different records. I know you can help me and I'm sure there's a way. I can get it in theory, but I don't know the syntax well enough. Thanks in advance. Kenyell OllieWeb DeveloperABC Financial Services" |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-14 : 08:31:02
|
this should do it:select t1.* from MyTable t1 inner join (select id, max(contact_dt) as contact_dt MyTable group by id) t2 on t1.id = t2.id and t1.contact_dt = t2.contact_dtGo with the flow & have fun! Else fight the flow  |
 |
|
|
Corobori
Posting Yak Master
105 Posts |
Posted - 2004-10-19 : 20:59:51
|
quote:
Originally posted by spirit1
this should do it:
Great stuff !There was just a tiny problem, a FROM was missing. It should be like this:select t1.* from MyTable t1 inner join (select id, max(contact_dt) as contact_dt From MyTable group by id) t2 on t1.id = t2.id and t1.contact_dt = t2.contact_dtjean-lucwww.corobori.com |
|
|
|
VIG
Yak Posting Veteran
86 Posts |
Posted - 2004-10-20 : 00:53:38
|
| select t1.*from MyTable t1 where t1.contact_dt= (select top 1 contact_dt from MyTable group by contact_dt order by count(*) desc) |
|
|
|
|
|
|
How do I show only the records that are the most recent