1

I'm sorry for not being more clear in the title but I didn't know what to put there, if anyone has a suggestion based on my question, please let me know so I can change it. I have also included an SQL Fiddle below, it might be helpful to understand my explanation.

I'm currently creating a query that is supposed to return information about some report documents. These documents have a status which is saved in another table. Every time a document's status is updated a new record will be created in the Report_Status table. My goal is to show all these report documents with the current status together with some relevant information. This information is included in my fiddle but the problem lies mainly with the Report_Status table.

The solution I have right now gives the following output:

|        Study |           Site | Visit |         Report |              Status |
|--------------|----------------|-------|----------------|---------------------|
| Tester (UAT) | Mercy Hospital |   SQV | SQV Report_GSG | Authoring (Default) |
| Tester (UAT) | Mercy Hospital |   SQV | SQV Report_GSG | Authoring (Default) |
| Tester (UAT) | Mercy Hospital |   SQV | SQV Report_GSG | Authoring (Default) |
| Tester (UAT) | Mercy Hospital |   SQV | SQV Report_CoE | Authoring (Default) |
| Tester (UAT) | Mercy Hospital |   SQV | SQV Report_CoE | Authoring (Default) |
| Tester (UAT) | Mercy Hospital |   SQV | SQV Report_GSG |      Under Revision |

I actually only need 1 entry for each report i.e. SQV Report_GSG with Status 'Under Revision', and SQV Report_CoE with Status 'Authoring Default' but I'm not able to manupilate the data accordingly.

I have already tried filtering for the MAX(id) but apparently there is an inconsistency in the data which causes the latest record to NOT have the highest ID. The inspiration for this action came from the second answer in this question: SELECT DISTINCT on one column

Please find my current "solution" in the SQLFiddle: http://sqlfiddle.com/#!7/9983a/22

The data you find in the Report_Status table in the example contains data that corresponds with the actual data i.e. id, create_date and update_date.

Please keep in mind that the software I'm using to create this query on does NOT support T-SQL or PL/SQL commands.

I hope I was clear, if not, please don't hesitate to ask!

Community
  • 1
  • 1
Gravinco
  • 687
  • 1
  • 9
  • 28
  • 1
    This is typically solved with window functions and `row_number()` –  Feb 23 '17 at 14:30
  • 1
    Which RDBMS is this for? It often does make a difference whether you're using MySQL, PostgreSQL, Oracle, SQL Server or IBM DB2 - or something else even. Please add a relevant tag to your question! – marc_s Feb 23 '17 at 14:53
  • @marc_s I did some investigating and discovered that the software I have to use connects to a MySQL database. Hope this helps! – Gravinco Feb 23 '17 at 17:39

0 Answers0