1

Good Day,

I am currently having trouble with a select statement in SQL. I am trying to extract data from tables. The select statement returns multiple rows for the same record because there has been different individuals for this record. However I do want to see the latest individual row.

For Example Result Tab:

Row 1: 1 entry1 yes yes yes 20 50 Dawid

Row 2: 2 entry1 yes yes yes 20 50 Jan

Row 3: 3 entry1 yes yes yes 20 50 Charlie

Required Result Tab:

Row 3: 3 yes yes yes 20 50 Charlie

The only reference I have to use is the primary key unfortunately.

The ENT is for the entry table, the instance links to the workflow and from the instance to the current workflow progress. I need help to only return the latest 1 of all the results per entry. Is this possible?

I can not use a CTE or any variable declares, because I am going to generate a SQL Report which does not support it.

LEFT JOIN ENT_Instance ENTI ON ENT.entryID = ENTI.entryID
LEFT JOIN VW_Instance WFI ON ENTI.instanceID = WFI.instanceID
LEFT JOIN WF_Progress wfpg ON WFI.instanceID = wfpg.instanceID

Any assistance regarding this would be appreciated.

Wimpie Norman
  • 27
  • 5
  • *"However I don't want to see the latest individual row"* and then you show the "last" row in your required results. Which is it, you do or don't? – Thom A Nov 09 '20 at 13:18
  • *"I can not use a CTE or any variable declares, because I am going to generate a SQL Report which does not support it."* If you're constrained by an application, you should be tagging that application as well. – Thom A Nov 09 '20 at 13:18
  • My apologies for my typo there Larnu - I only want to see the latest individual. I have also raised my concerns regarding the SQL package support of the application. It is a work in progress. – Wimpie Norman Nov 09 '20 at 13:22
  • Does this answer your question? [Retrieving last record in each group from database - SQL Server 2005/2008](https://stackoverflow.com/questions/4751913/retrieving-last-record-in-each-group-from-database-sql-server-2005-2008) – Thom A Nov 09 '20 at 13:23
  • Does this answer your question? [How to select the last record of a table in SQL?](https://stackoverflow.com/q/5191503/2029983) – Thom A Nov 09 '20 at 13:23
  • I could try it, but It does make use of a CTE table. Is there other methods out there by using your normal joins? – Wimpie Norman Nov 09 '20 at 13:25
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Thom A Nov 09 '20 at 13:26
  • *"but It does make use of a CTE table"* Then change it to a subquery. – Thom A Nov 09 '20 at 13:26
  • The second would not work, because I make use of joins and I require to select the last top 1 from a joined table. – Wimpie Norman Nov 09 '20 at 13:27
  • Having a `JOIN` doesn't change the answer, @Wimpie. – Thom A Nov 09 '20 at 13:28
  • Thank you Larnu, I will go and try these suggested once and see if I can implement them into my code. – Wimpie Norman Nov 09 '20 at 13:29
  • Isn't this just a simple select top 1 where (condition) orderBy Id desc – David Edel Nov 09 '20 at 15:51

1 Answers1

0

You can try to do like this :

SELECT a.*, p1.*
FROM ENT_Instance a
JOIN VW_Instance p1 ON (a.id = p1.ENTI)
LEFT OUTER JOIN ENT_Instance p2 ON (a.id = p2.ENTI AND 
    (p1.date < p2.date OR (p1.date = p2.date AND p1.id < p2.id)))
WHERE {condition};
mandarin software
  • 197
  • 8