Return one SQL row per product with price and latest date
I am not a SQL guy, I have used it in the past and rarely have an issue that cant be solved by google... however this time I need to ask the Community.
I have a database with a table called 'Transactions' it has data like this:
ProdNo | Price | TransactionDate | PurchasedBy | etc..... ---------------------------------------------------------- 3STRFLEX | 13.02 | 20162911 | AWC | ..... 3STRFLEX | 15.02 | 20162011 | DWC | ..... 3STRFLEX | 15.02 | 20160101 | AWC | ..... AFTV2 | 35.49 | 20162708 | AWC | ..... AFTV2 | 29.99 | 20160106 | DWC | ..... AFTV2 | 29.99 | 20160205 | AWC | .....
The desired output is:
ProdNo | Price | TransactionDate ----------------------------------- 3STRFLEX | 13.02 | 20162911 AFTV2 | 35.49 | 20162708
I have tried a to write this myself and I ended up with SQL like this:
select t.ProdNo, t.TransactionDate as 'LastPurchaseDate', t.Price from Transactions t inner join ( select ProdNo, max(TransactionDate) as 'LastPurchaseDate' from Transactions WHERE Price > 0 group by ProdNo ) tm on t.ProdNo = tm.ProdNo and LastPurchaseDate = tm.LastPurchaseDate
However in my data set this returns (cut down) which shows multiple rows per product
ProdNo | LastPurchaseDate | Price 3STRFLX | 20120924 | 0.000000 3STRFLX | 20120924 | 22.000000 3STRFLX | 20150623 | 0.000000 3STRFLX | 20150623 | 1.220000 3STRFLX | 20150623 | 1.222197
So to confirm: I would like 1 row per product for the latest date it was purchased regardless of the price, but I need the price in the returned data.
You can use a CTE and the ranking function
WITH CTE AS ( select t.ProdNo, t.TransactionDate as 'LastPurchaseDate', t.Price, rn = row_number() over (partition by ProdNo order by TransactionDate desc) from Transactions t ) SELECT ProdNo, LastPurchaseDate, Price FROM CTE WHERE RN = 1
select t.username, t.date, t.value from MyTable t inner join ( select username, max(date) as MaxDate from MyTable group by username ) tm on eTour.com is the newest place to search, delivering top results from across the web. Content updated daily for how to learn sql.
You're on the right track. What if you use:
select t.ProdNo, t.TransactionDate as 'LastPurchaseDate', t.Price from Transactions t inner join ( select ProdNo, max(TransactionDate) as 'LastPurchaseDate' from Transactions WHERE Price > 0 group by ProdNo ) tm on t.ProdNo = tm.ProdNo and t.TransactionDate= tm.LastPurchaseDate
Note the change in join conditions.
What happened in your query:
LastPurchaseDate = tm.LastPurchaseDate. There is only one column called
LastPurchaseDate, so it's equating it with itself, which is always true. So you're left with
t.ProdNo = tm.ProdNo, since
t.ProdNo is not unique, you get multiple records returned for each
How to find the oldest or most recent record associated with a This is straightforward in SQL. to display a list of users next to the date of their most recent order. SELECT user_id, MAX(created_at) FROM orders GROUP BY the price and quantity or maybe you need to perform a calculation on them. If multiple products have the same unit price as the lowest price, the query will return more than one row. SQL MIN function with GROUP BY clause example. To find the lowest unit price of the product in each category, you use the MIN() function with a GROUP BY clause:
One method uses the ROW_NUMBER windowed function.
My query uses a common table expression (CTE) to provide sample data. When using this technique you always need a CTE, or subquery. This is because values generated in the SELECT clause are not available to the WHERE clause. This is a consequence of something called the Logical Processing Order. In other words; SQL Server generates the row numbers after it has filtered the data. CTEs/Subqueries provide you with a second WHERE clause, that is actioned after the row numbers have been generated**.
-- Returning the most recent record from a transaction table. WITH SampleDate AS ( SELECT ROW_NUMBER() OVER (PARTITION BY ProdNo ORDER BY TransactionDate DESC) AS Rn, * FROM ( VALUES ('3STRFLEX', 13.02, '20162911', 'AWC '), ('3STRFLEX', 15.02, '20162011', 'DWC '), ('3STRFLEX', 15.02, '20160101', 'AWC '), ('AFTV2' , 35.49, '20162708', 'AWC '), ('AFTV2' , 29.99, '20160106', 'DWC '), ('AFTV2' , 29.99, '20160205', 'AWC ') ) AS x(ProdNo, Price, TransactionDate, PurchasedBy) ) SELECT * FROM SampleDate WHERE Rn = 1 ;
** Actually this isn't entirely true. It is called the logical order for a reason. SQL Sever can/will exeucte your queries any way it sees fit. But however your query is physically executed it will respect the logical order.
Invoice Date]) AS MaxDate FROM [Food purchase data] AS FPD1 INNER JOIN Item ,FI3.Item ,FPD3.[Price per unit] ,FPD3.[Purchase unit] ,I3.[Invoice Date] The following query would return one row per [Food item ID] . Hi all, I have survey data with multiple people taking the survey mutiple times. Each row is a survey response with a date taken. The only way I know which is the "pre survey" and which is the post survey is by the date taken. I would like to create a "prepost Colunm that read the date and design
Try this. by using row_number().
select * from ( select T.ProdNo, T.TransactionDate as 'LastPurchaseDate', T.Price, row_number() over (partition by ProdNo order by TransactionDate desc) as rnk from Transactions T )a where rnk='1'
Skinning Q#1 independently and slightly differently than @ypercube with cte as (select row_number() over (partition by product_id, invoice_id order by dt desc) I have a MySQL table like this: Product, Price, Timestamp Shoes, 100, 1069351300 Shoes, 105, 1069351200 Shirt, 50, 1069351150 Ties, 10, 1069351200 Ties, 5, 1069351205 I want to do a SELECT that grabs the newest price for each product. I started with this: SELECT * FROM prices GROUP BY product; but this gives me the first entry of each product, not the one with the highest timestamp. Adding
Pls Try this --If one ProdNo has two same max TransactionDate, will return both two rows
select ProdNo,Price,TransactionDate from Transactions t where not exists (select 1 from Transactions where ProdNo=t.ProdNo and TransactionDate>t.TransactionDate)
Because the order of rows stored in a table is unspecified, the SELECT TOP you to return more rows with values that match the last row in the limited result set. if two or more products have the same prices as the most expensive product, Data Types · BIT · CHAR · DATE · DATETIME2 · DATETIMEOFFSET · Decimal I am trying to accomplish several things here - but the first goal is to determine the most recent or last transaction for each customer. I have tried about 8 different things but fall a little short each time in accomplishing the real goal: The Sales info and the Customer are two separate tables
SQL Server MAX() function is an aggregate function that returns the maximum To find the product with the highest list price, you use the following statement:. I’m a founder, author of several books, and creator of various open-source software. I write about topics such as technology, mindfulness, and fitness, and I tweet at @xaprb.
The table has a date specifying the time of the record insert (or something like an inventory scan time); You don't want to see multiple records for Is one way. SELECT id, id1, date, encrypt FROM table o WHERE date = (SELECT MAX(date) FROM table i WHERE o.id = i.id) Is another. Khurram: ROWID is the physical address of the row on disk and has no connection at all to either the order of insertion in the table, or the value of any particular column.
This is the SQL statement, separated into logical sections for ease of National Standards Institute) rules and should work in the latest versions of all Order Details Extended: Calculates the Extended Price, the discounted total for each line item (order detail) in all Run the query - notice that there is one row per product. Find Database Sql now. Visit & Look for More Results!