The second thing you could do is add to the inventory table a mostrecent bool column. Create unique index on inventory (good) where mostrecent; You would then want to use triggers to set mostrecent to be false when a new row for a good was inserted. This adds more complexity and greater chances for bugs but it is helpful. SQL queries to only show most recent purchase records for individual food items. Ask Question 8. I'm working with a food purchasing / invoice system in MS Access 2013 and am trying to create an SQL query that will return the most recent purchase price for each individual food item.
Hello I have a table with columns:
*using oracle
I want to know how i can get the most recently added document for a given user.
Thanks
Matt
MattMatt1,7838 gold badges25 silver badges53 bronze badges
6 Answers
sh_kamalhsh_kamalh3,3363 gold badges32 silver badges50 bronze badges
Not sure of exact syntax (you use varchar2 type which means not SQL Server hence TOP) but you can use the LIMIT keyword for MySQL:
Or rownum in Oracle
If DB2, I'm not sure whether it's TOP, LIMIT or rownum...
gbngbn353k60 gold badges499 silver badges587 bronze badges
With SQL Server try:
SELECT TOP 1 *FROM dbo.youTableWHERE user_id = 'userid'ORDER BY date_added desc
6,2863 gold badges27 silver badges31 bronze badges
Assuming your RDBMS know window functions and CTE and USER_ID is the patient's id:
I assumed you wanted to sort by DOCUMENT_DATE, you can easily change that if wanted. If your RDBMS doesn't know window functions, you'll have to do a join :
It would be good to tell us what your RDBMS is though. And this query selects the most recent date for every patient, you can add a condition for a given patient.
Vincent SavardVincent Savard26.5k10 gold badges57 silver badges67 bronze badges
You haven't specified what the query should return if more than one document is added at the same time, so this query assumes you want all of them returned:
This query will only make one pass through the data.
Jeffrey KempJeffrey Kemp49.5k12 gold badges93 silver badges136 bronze badges
pavanredpavanred
4,75812 gold badges45 silver badges58 bronze badges
Not the answer you're looking for? Browse other questions tagged sqloracleselectdate or ask your own question.
I have a report I am creating through an ODBC connection. The report includes several invoices, where each invoice has several products. There is also a table which contains all the historical price changes for each product (field: unit-price). Currently there are duplicate product records being pulled, one for each time there was a price change. Therefore, I need to filter my data so that only the most recent unit-price is shown (date field: effective-date). How can I do this via the 'Select Expert?'
In short, show the product's unit-price for the most recent effective-date.
Thank you!
user3616771user3616771
2 Answers
You'll need to create a sql-expression field to get the most-recent effective date, then use this field in the record-selection formula.
Record-selection formula:
craigcraig17.4k17 gold badges80 silver badges151 bronze badges
Instead of doing it in select expert. group by effective date and set the ordering as
SivaSivaDescending
.7,9609 gold badges29 silver badges49 bronze badges