November 24, 2009 at 1:27 pm
The query below return many records where the description field is identical, where that occurs I want only the most recent of those records.
select historyid, h.startdate,a.account,h.description,longnotes, address1, city, state, postalcode, h.category
from sysdba.history h
join sysdba.account a on a.accountid = h.accountid
join sysdba.address r on r.addressid = a.addressid
Suggestions are appreciated!
November 24, 2009 at 1:36 pm
Ron-279530 (11/24/2009)
The query below return many records where the description field is identical, where that occurs I want only the most recent of those records.select historyid, h.startdate,a.account,h.description,longnotes, address1, city, state, postalcode, h.category
from sysdba.history h
join sysdba.account a on a.accountid = h.accountid
join sysdba.address r on r.addressid = a.addressid
Suggestions are appreciated!
Not sure what columns belong to what tables. In multi-table queries it would be helpful if you slaised ALL the columns. They may be unique in the query, but you never know when a "duplicate" column name may be added to an existing table or another table added that has a duplicate column name.
November 24, 2009 at 1:43 pm
Good point!
Here it is...
select h.historyid, h.startdate,a.account,h.description,h.longnotes, a.address1, a.city, a.state, a.postalcode, h.category
from sysdba.history h
join sysdba.account a on a.accountid = h.accountid
join sysdba.address r on r.addressid = a.addressid
November 24, 2009 at 4:36 pm
bump
November 24, 2009 at 6:17 pm
If you're going to "bump" your post, how about posting something usefull, like DDL for your tables, sample data, and expected results. That may actually get you more help faster. As it is, we are all voluteers here with our own jobs that we have to work. Sometimes these forums have to wait while we earn our pay.
November 24, 2009 at 8:10 pm
Lynn Pettis (11/24/2009)
If you're going to "bump" your post, how about posting something usefull, like DDL for your tables, sample data, and expected results. That may actually get you more help faster. As it is, we are all voluteers here with our own jobs that we have to work. Sometimes these forums have to wait while we earn our pay.
Seconded. Bumping on these forums is more likely to get you ignored than get you helped, especially if you do it in an obvious manner that adds nothing... and even more so when it is only a couple hours after your initial request.
November 25, 2009 at 7:50 am
THanks for your feedback
November 25, 2009 at 2:00 pm
Ron-279530 (11/25/2009)
THanks for your feedback
Heh... I'm going to assume that you still don't have an answer to your original question but, first...
I notice from your post count that you're still a bit of a newbie on this forum. A lot of us like to actually test any solution we may post before we post it. That's why Lynn asked about posting the CREATE statment for the tables and a bit of sample data. Even with that, there's a right way and a wrong way. If you were to study the article located at the first link in my signature line below and do things that way, there'd be no need to "bump" (also covered in that article) your own post because people will beat your door down trying to be the first to provide and answer. It'll take you a little bit to get used to posting data correctly but the article has some good suggestions on how to do such a thing quite easily. While it seems to be a bit of a PITA at first, you'll love the results because it 1) shows people that you're actually interested in getting your problem solved quickly and accurately and 2) many of us answer a whole lot of posts each and every day... guess which ones we're likely to do first and which ones may never get an answer?
Ok... onto your problem which will be, of course, untested because you haven't provided the necessary data to actually do a test and I'm not going to take the time to setup such data. 😉
The first thing to realize is that this is a two pronged problem... you must first find the latest history startdate and related history ID for each given account. Then, the rest is easy. We'll find the lastest information using what is known as a "Derived table" (can't use a CTE in SQL Server 2000 but they're similar) and then join to that to get the rest of the information.
select h.historyid, h.startdate,a.account,h.description,h.longnotes, a.address1, a.city, a.state, a.postalcode, h.category
from (--==== Derived table "maxdate" finds max history start date for each account
SELECT a.account, MAX(h.startdate) AS MaxStartDate
FROM sysdba.history h
JOIN sysdba.account a
ON h.accountid = a.accountid
GROUP BY a.account
) maxdate
join sysdba.history h on h.accountid = maxdate.accountid
join sysdba.account a on a.accountid = maxdate.accountid
join sysdba.address r on r.addressid = a.addressid
--Jeff Moden
Change is inevitable... Change for the better is not.
November 25, 2009 at 8:12 pm
Jeff, a small correction.
It should be:
...
join sysdba.history h on h.accountid = maxdate.accountid AND h.startdate = maxdate.MaxStartDate
...
🙂
_____________
Code for TallyGenerator
November 25, 2009 at 9:54 pm
Sergiy (11/25/2009)
Jeff, a small correction.It should be:
...
join sysdba.history h on h.accountid = maxdate.accountid AND h.startdate = maxdate.MaxStartDate
...
🙂
Heh... now you know why I like to test my stuff... thanks for the catch, Sergiy.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply