Solved

SQL Syntx for getting the latest record in a table

Posted on 2012-12-27
9
316 Views
Last Modified: 2012-12-28
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.
0
Comment
Question by:ISBTECH
  • 5
  • 3
9 Comments
 
LVL 8

Expert Comment

by:hpdvs2
ID: 38725021
Presuming AutoIncrementing Id's,


WHERE Id = Max(Id)
0
 
LVL 8

Expert Comment

by:hpdvs2
ID: 38725027
http://www.w3schools.com/sql/sql_func_max.asp

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

Author Comment

by:ISBTECH
ID: 38725031
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."
0
 

Author Comment

by:ISBTECH
ID: 38725040
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.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 8

Assisted Solution

by:hpdvs2
hpdvs2 earned 250 total points
ID: 38725167
Ahhh,  Aggregrate functions essentially merge data, so you cant use them list the same field without grouping the others...

But it sounds like what you need is :

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)


--- So the idea is that you select all the rescords from table t1, where the action code is 102, and where the same sales id does not exists with another record that has greater than 102.
0
 

Author Comment

by:ISBTECH
ID: 38725233
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.
0
 

Author Comment

by:ISBTECH
ID: 38725282
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?
0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 38725606
did you try this?

Select * FROM Table1 t1 WHERE t1.ActionCode=102
AND NOT EXISTS
(SELECT 1 FROM Table1 t2 WHERE t2.ActionCode > 102 AND t2.SaleId = t1.SaleId)

Open in new window

0
 

Author Closing Comment

by:ISBTECH
ID: 38726860
Thanks both you that was amazing!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now