Getting latest record and a key

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)

Getting latest record and a key

Author

Topic

jgandara
Starting Member

18 Posts

Posted - 2002-09-04 : 12:56:10

I have a table with transactions. so far i have these columns:
TransactionID - Autonumber
Date- Transaction Date
Serial Nbr - key from a catalog table

The transactionId is the key of this table. I want to get the latest(current) record. Because I can have duplicated dates I do the follow:
Group by date(max) and serial number - View1. Out Serial#,Date
Join View1 + myTable on Serial# and date and grouping by serial#, transactionId. Out Serial#,TransactionId
So in order to get the latest record by transactionId I have to do 2 joins, and sometimes that is slow for reports.

Any suggestion how to deal with it?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-09-04 : 13:00:42

show us some sample (invent value if need be) data....it'll clarify things....there's nothing better than seeing data to understand a problem.

but couple of things first....

what about "select top 1 from table1 order by date desc"
or "select top 1 from table1 order by TransactionID desc"

the 2nd should give you the record with the biggest autonumber value....and thus the last record inserted....
is that not what you want?

jgandara
Starting Member

18 Posts

Posted - 2002-09-06 : 17:23:25

First I get the maximum date for a serial number
*******************************************
CREATE VIEW dbo.vwCurrentAssetTransaction
AS
SELECT SerialNbrID, MAX(ShippingEffectiveDate) AS MaxShippingEffectiveDate
FROM tblAssetTransaction
GROUP BY SerialNbrID
*******************************************

then doing a join by date and serial #, I get the latest transaction (assuming the maximum number is first)
*******************************************
CREATE VIEW dbo.vwCurrenTransactionbyTransId
AS
SELECT tblAssetTransaction.SerialNbrID, MAX(tblAssetTransaction.TransactionID) AS MaxTransactionID
FROM vwCurrentAssetTransaction INNER JOIN tblAssetTransaction ON vwCurrentAssetTransaction.SerialNbrID = tblAssetTransaction.SerialNbrID
AND vwCurrentAssetTransaction.MaxShippingEffectiveDate = tblAssetTransaction.ShippingEffectiveDate
GROUP BY tblAssetTransaction.SerialNbrID
*******************************************

These ia some sample data:

Serial # Shipping effective TransactionId
0010492BPP 7/31/2000 5
0010493BPP 7/31/2000 50958
0010493BPP 8/28/2000 6

What i want to get is the latest transactionid for a given serial#. the latest transactionid is the one with the latest date and maximum transactionid. To get that I did those 2 vies, so I use the second one for reports, but it's slow in some queries.

I want to be able to retrieve the latest TransactionId for all the records at one point, not only for one serial#

Thanks

Edited by - jgandara on 09/06/2002 17:25:23

jgandara
Starting Member

18 Posts

Posted - 2002-09-12 : 11:10:13

Please help on this!!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-12 : 11:18:33

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=18002

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources