Avatar of ISBTECH
ISBTECH
Flag for United States of America asked on

SQL Syntx for getting the latest record in a table

I have a table that logs activities with a transfer of data to a third party company, there are multiple transfers of data per sale as the process of finalizing the sale proceeds.  The table tracks the individual sale through a unique SalesID and then records each transfer of data via a sequence number and an action code.  So the first transfer of data for a particular sale would be sequence number 1 and say action code 1, then later in the process we transfer again and I would then see sequence number 2 and action number 102, then later there may be up to two additional transactions sequenced as 3 and 4 with action codes of 299, 205 etc.  I need to be able to pull data from this table for transactions that have reached action code 102 but no further in the process.  I know there is a MAX command that takes the biggest number in a table but I'm unsure how to use it in this case.
Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
ISBTECH

8/22/2022 - Mon
Dan Violet Sagmiller (He/Him)

Presuming AutoIncrementing Id's,


WHERE Id = Max(Id)
Dan Violet Sagmiller (He/Him)

http://www.w3schools.com/sql/sql_func_max.asp

SELECT MAX(FieldName) FROM TableName
 - returns single row/field with max value of FieldName.
ISBTECH

ASKER
That was my first thought, I did a select * from the table where max(actioncode) = 102 and I get back

"An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ISBTECH

ASKER
That will give me the maximum value of the action code in the table correct?  I'm looking for all the records in which the largest value for action code would be 102.
SOLUTION
Dan Violet Sagmiller (He/Him)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ISBTECH

ASKER
Ok almost there!  When I run that I get 760 rows of data, one row each for sequence number 1 action code 0 and sequence number 2 action code 102 but I just need the record 1 time for each sale so I tried

SELECT * FROM Table t1 WHERE SaleId IN
(Select SaleId FROM Table WHERE ActionCode=102)
AND NOT EXISTS
(SELECT SaleId FROM Table WHERE ActionCode <> 102 AND SaleId = t1.SaleId)

but then I get only about 8 records which is nowhere near the 380 rows that I would expect based on the results prior to the change.
ISBTECH

ASKER
Ah I see, it's because i'm selecting everything and the action date and sequence number are different in each record, any way I can screen the actioncode 0 transactions from my results?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Sharath S

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ISBTECH

ASKER
Thanks both you that was amazing!