Last function not accurate..

[boostbyslinky]

Morning folks,

Just wondered if anyone had any thoughts on how I can get this query to be accurate! The LAST function doesn't appear to be finding the last entry every time, in fact having just refreshed the query a number of times it has presented a different priority value each time!

I've pasted the query below if anyone is interested in trying to help me work this out..


SELECT DISTINCT dbo_ReportData.docket, dbo_ReportData.site_search, dbo_ReportData.Name, Last(dbo_ReportData.Priority) AS LastOfPriority, Min(dbo_ReportData.[Job No]) AS [MinOfJob No], Max(dbo_ReportData.[Job No]) AS [MaxOfJob No]
FROM dbo_ReportData
WHERE (((dbo_ReportData.[log num]) In (1,2,3,5,6,12,24,36,99,8,14)) AND ((dbo_ReportData.docket) In (SELECT DISTINCT dbo_ReportData.Docket
FROM dbo_ReportData
WHERE (((dbo_ReportData.[Completion Date])>=Date()-31))
GROUP BY dbo_ReportData.Docket))
GROUP BY dbo_ReportData.docket, dbo_ReportData.site_search, dbo_ReportData.Name;

Thanks in advance..

 

[stopher]

The order of records is arbitrary unless you sort them. I see you've used Group By but are you sure you've ordered your query right down to the most detailed level? See here for explanation.

 

[boostbyslinky]

The order of records is arbitrary unless you sort them. I see you've used Group By but are you sure you've ordered your query right down to the most detailed level? See here for explanation.

Ahh.. That makes sense..

This is output from a helpdesk system, docket is the lifespan reference of the call, job no is the unique event identifier, so if I sort by Docket, then Job No, I should be able to use last of priority more accurately?

 

[Galaxiom]

Like many developers I don't trust the First or Last functions at all. It was a long time ago but I had a case where the First of records sorted ascending gave a different answer from the Last of the same records sorted descending. Go figure.

I always use the Max and Min.

 

[boostbyslinky]

Like many developers I don't trust the First or Last functions at all. It was a long time ago but I had a case where the First of records sorted ascending gave a different answer from the Last of the same records sorted descending. Go figure.

I always use the Max and Min.

Max and Min do indeed provide more accurate results, unfortunately the priority figure (which is the one I'm most interested in at this point) can go up as well as down during the lifespan of a call, hence I need to use the Last value..

I am beginning to wonder why I embarked on this bloomin mission!

 

[Uncle Gizmo]

Like many developers I don't trust the First or Last functions at all. It was a long time ago but I had a case where the First of records sorted ascending gave a different answer from the Last of the same records sorted descending. Go figure.

I always use the Max and Min.

That’s what I like about this forum, you have just confirmed something that I now realise I also do.

My results from first and last were always suspect, this indicated to me that either first and last didn’t work properly, or I didn’t understand how it worked. I suppose I could have persevered and sort it out, but like you I found alternatives and avoided using something I don’t understand.

I was reading a thread earlier somewhere else about the tree view control, again something I have always avoided, and again at least two other people expressed that they had come to a similar conclusion.

I do like the look of the result returned by the tree view control but I’m not sure it will be worth the effort to learn.

 

[Brianwarnock]

IMHO anybody with data that needs the first and/or last to be found should have it date/time stamped.

The wonderful Pat Hartman gave a great discourse on First/Last , I wish I had kept it because I cant find it, perhaps it was lost in the hijack.

Brian

 

[boblarson]

The Last and First functions do not work as one would expect. This is a documented issue at Microsoft as well and they have not chosen to fix it. It will only pull a random record when using it, regardless of whether you use it with a sort order on a query, etc.

If you want to find the last record entered in the table you can use DLast and that will work. It will also find the last record entered which matches criteria. So if you have a table like this (I'm listing it in the order entered):

MyID       MyText
1            Something
2            One More
3            Keep On
4            Just Another

And you used DLast just on the table you would get ID of 4 and Text of Just Another. If you had a query that limited the ID's to 3
i.e. SELECT * FROM MyTable WHERE MyID <= 3
Then it DLast on that query would return 3, Keep On
And for the very first record entered you can use DFirst.

One thing - Microsoft's own documentation says that DLast and DFirst act the same way as First and Last and that is erroneous and I have been working to try to get them to change that documentation. I have done some extensive tests with DFirst and DLast and they work consistently as I've said.

 

[boostbyslinky]

Thanks for that Bob.. Any thoughts on how I would apply that into my current query? On average it outputs about 4,800 rows so this would need to iterate through each of those..

 

[boblarson]

Use a subquery to pull the value and use that subquery in with the other query.

 

[boostbyslinky]

I've managed to resolve my issue, although I'll be honest and say that I've not used Last or Dlast..

As I didn't want the Max priority figure, I wanted the priority figure attached to the last job number, I've added a second query to deliver priority and product details on all of the job numbers attached to dockets processed in the last 31 days, I'm then using VLOOKUP to use the MAX of job number to find the last priority..

It's not the most graceful of solutions (we all know that VLOOKUP tends to get a little slow when you get into thousands of records), but it works and the data returned is finally accurate!

Thanks for the help folks, I think this discussion might have been helpful to others too..