Last(), the heck is it doing???? (1 Viewer)

[smbrr]

Hello,

I'm trying to use the last() function in my queries, but its behaviour transcends everything I am able to fathom.

Please advise.


Assume two tables.

-------------                  -------------
RStatusList                    Status
-------------                  -------------
RequestID (int)                ID (int)
StatusID (int)                 label (string)
ChangeDate (Date)              -------------
-------------

The point is to get the last status posted (according to ChangeDate).

Easy, I join them in a query; Except it didn't work very well so I ended up trying to at least get the ID of the last statut for now.

A simple query on just the 1st table.

- group by requestID, ordered asc for easy browsing
- last(ChangeDate), ordered asc, to get the last date
- last(statusID) because i want the last

SELECT RStatusList.RequestID, Last(RStatusList.ChangeDate) AS LastOfChangeDate, RStatusList.StatusID
FROM RStatusList
GROUP BY RStatusList.RequestID, RStatusList.StatusID
ORDER BY RStatusList.RequestID, Last(RStatusList.ChangeDate);

First, I remove these totals and run the query a simple select.

RequestID    | ChangeDate      | StatusID
1            | 10/09/2013      | 1
1            | 14/08/2014      | 2
1            | 14/08/2014      | 4
2            | 30/10/2013      | 1
2            | 10/04/2014      | 4
3            | 16/01/2014      | 1
3            | 10/04/2014      | 2
3            | 30/06/2014      | 4

Those are the first 3. Let's focus on request #3, it has 3 obviously separate dates, the last one is obviously 30/06/2014 with a status 4.

Now I run the above query with my group by and last() functions.

RequestID    | ChangeDate      | StatusID
1            | 14/08/2014      | 2
2            | 10/04/2014      | 4
3            | 10/04/2014      | 2

What? As you can see, it somehows selects the status 2 for my requestID 3, with the date 10/04/2014.
It's neither the last date in terms of chronology nor in terms of physical position, same for the statusID itself, it isn't even the last one in terms of numbers.
The last() did not take the very last item of the list either because if it did, all the rows would be the same date and status (namely 29/05/2015 and status 1 because the last request has been added today).

Initially, I ran the query with an inner join on my status table to fetch the label right away, and it worked a lot better, but I had some wrong status every now and then, so I thought making it two queries would work better (first find the right status ID with grouping and such, and then perform a join on the status table).

By running my first query (which is exactly the same but with a join on status.ID and the last() being on label), I get this result:

RequestID    | ChangeDate      | Label
1            | 14/08/2014       |  Closed
2            | 10/04/2014       |  Closed
3            | 30/06/2014       |  Closed

Which is actually correct. Closed is status 4, and the date is 30/06/2014, everything is working (except it doesn't always work, and that's why i started to fiddle with it)

I tried looking online for the last() function but everything I find is basic stuff about it, stuff that is implied by its name anyways. It doesn't seem to be that complex of a function so what in love's name am I not getting?

If you made it this far, please advise

Thank you

 

[namliam]

First and last functions I find are not really reliable.... If you have a requirement like this best to write proper SQL for it.

Simply get the Max(ChangeDate) per RequestID in a (sub) query first.
Then join this to your rstatuslist table to only display the last record.

Obviously you will have issues with requestID #1 since your changedate lacks a time component, perhaps you can/should get a max(StatusID) as well?

 

[smbrr]

Oh, I didn't think of using Max(). I'll try this. As for the dates in the same day, I have a lot of those, but doesn't the field still contain the time data even if it doesn't display it?

I can't really do Max(statusID) because sometimes stuff is in testing phase (status 3) and goes back to wip (status 2).

 

[smbrr]

Ok, I got it working quite well with Max(), the only time i'll get a wrong status will be if a status is downgraded in the same day it was upgraded.

Not perfect but it'll do, maybe I'll change it to take time data as well.

Thank you for this quick answer.

...that said, I would really like to understand why is last() doing what it does. Closing the thread anyways.

 

[Brianwarnock]

Does this link help

https://support.microsoft.com/en-us/kb/208190/en-us

Brian

 

[smbrr]

Yeah, it does. I understand now that the function ignores sort orders and even index/primary key order and takes the last row added.

But in my case, it's literally what I'm looking for. The last added status for a request. According to what I read in this article, it should find it.

It might work differently with aggregations, this article doesn't cover the topic.

PS: kind of funny how the author types "the functions appear to ...". Aren't they supposed to be sure about it?

 

[Brianwarnock]

He uses "appear" in the symptoms paragraph, under cause he is specific.

Brian

 

[smbrr]

Oh, it "appears" that way to the user, my bad.