What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Syntx for getting the latest record in a table
Posted on 2012-12-27
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
3
9 Comments
http://www.w3schools.com/sql/sql_func_max.asp
SELECT MAX(FieldName) FROM TableName
- returns single row/field with max value of FieldName.
SELECT MAX(FieldName) FROM TableName
- returns single row/field with max value of FieldName.
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."
"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."
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.
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.
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.
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.
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.
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?
Featured Post
Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 1 hour left to enroll
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.