Finding the most recent date - SQL Server Forums

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

SQL Server Development (2000)

Finding the most recent date

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-01 : 07:56:32

Jennifer writes "I have 2 tables. 1 that stores notes information, 1 that stores information on a particular account. You can have multiple notes to 1 account but not multiple accounts to 1 note.

I use an ID field as my link between the 2 tables.

I am creating a report in which I have to list the latest activity on that account as well as details about the account. The notes field is a text field so cannot be used in the group by clause, nor in a select distinct.

In my current query I have something like

select table2.notes, table1.first_name, table2.last_name, table2.company_name from table1 left outer join table2 on table1.id = table2.id

Obviously if there is more than 1 note, the results return more than 1 row for that particular account....

How can I get the relevant data returned with the latest date (there is a date stamp in the notes table for when the note was created) for the notes...

SQL Server 2000
Windows 2000 Server"

Nazim
A custom title

1408 Posts

Posted - 2002-07-01 : 08:34:00

Derived query might help you. if have understood your question correctly this might do the trick.

select t2.notes, table1.first_name, table2.last_name, table2.company_name from table1
left outer join
(select id,notes from table2 t
inner join
(select id,max(datetimefield)
from table2
group by id) k
on k.id=t.id and k.datetimefield=t.datetimefield
) t2
on table1.id = t2.id

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Edited by - Nazim on 07/01/2002 08:35:03

Edited by - Nazim on 07/02/2002 02:15:33

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 10:05:08

I like the having clause for stuff like this

select table2.notes, table1.first_name, table2.last_name, table2.company_name
from table1 left outer join table2 on table1.id1 = table2.id1
group by table1.id1
having max(table2.datecolumn)

[edit] bleh, one sec.. editting statement[/edit]

-----------------------
Take my advice, I dare ya

Edited by - M.e. on 07/02/2002 10:06:49

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 10:08:47

hmm, I'm thinking this instead

select table2.notes, table1.first_name, table2.last_name, table2.company_name
from table1 left outer join table2 on table1.id1 = table2.id1
group by table1.first_name
having max(table2.datecolumn) = table2.datecolumn

-----------------------
Take my advice, I dare ya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources