Need help with building a query with LAST function

I have Query A, with fields ColA, ColB, Col C and ColD, and sorted in ascending order of ColA.

I wish to build Query B with fields ColB, Col C and the LAST (ColD) that appears in Query A.

I tried to build the query using SELECT .... GROUP BY. But as soon as I add Col B and Col C as the first two columns, it messes up the original sorting of Query A and affects the result of the function LAST (ColD).
Member_2_7966563Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Post  a concise and complete example.

But the problem is: LAST() requires an ORDER BY. GROUP BY is applied before ORDER BY and ignores that, so using LAST() in this context maybe not possible.

Also: Do you really need LAST(), not MAX()?

When your really need the value of the last row in a ordered set, then you need to exactly do that:

SELECT TOP 1 colD FROM yourTable ORDER BY colA ASC

Open in new window


And use this as (correlated) sub-query.
0
Pawan KumarDatabase ExpertCommented:
Pls provide input data and expected output.

First and last are available in sql 2012+
0
Máté FarkasDatabase Developer and AdministratorCommented:
You can use LAST as any other aggregation function.
But in Query B you have to add ColA not just ColB and ColC and must redefine sorting on ColA.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Máté FarkasDatabase Developer and AdministratorCommented:
Other Guys!
This is MS Access and not SQL Server!
LAST function works differently in Access than in SQL!
(actually LAST function does not exist in SQL Server only LAST_VALUE as windowing function).
0
Dale FyeOwner, Developing Solutions LLCCommented:
Personally, I dislike using First and Last because, as indicated by ste5an, these two require sorting and unless you sort by all of the other columns in a query, you may not get precisely what you think you will get as the First and Last values.  So, unless you are going to sort by all four of the columns (A, B, C, and D). I would recommend against that technique.

Is there nothing else (a date, ID value, some other alpha or numeric value) in the table(s) which contribute to your first query which would help you define the "last value in column D" associated with each value in column A?

Assuming that query1 returns:

A            B            C             D
1            1             8             11
1            2             6             14
1            3             9             10

then:

SELECT A, LAST(D) as LastDValue
FROM query1
GROUP BY A 

Open in new window

should return:
A       LastDValue
1             10

Based upon your statement:
"I wish to build Query B with fields ColB, Col C and the LAST (ColD) that appears in Query A."
you should be able to:
SELECT Q1.B, Q1.C, Q2.LastDValue
FROM query1
INNER JOIN query2 ON Q1.A = Q2.A

Open in new window

Written with a subquery that would look like:
SELECT Q1.B, Q1.C, Q2.LastDValue
FROM query1 as Q1
INNER JOIN (
SELECT A, LAST(D) as LastDValue
FROM query1
GROUP BY A 
) as Q2 ON Q1.A = Q2.A

Open in new window

0
Member_2_7966563Author Commented:
Dale, regarding your question, "Is there nothing else in the table(s) which contribute to your first query which would help you define the "last value in column D" associated with each value in column A?

Answer is Yes. Last Value in Column D that I am interested in, is always the one with the MAX value in Column A. That is why I am first sorting by Column A in a separate query, and then using the LAST function on Column D. Was there an easier way to do this?
0
PatHartmanCommented:
First and Last relate to the first and last row of a recordset.  They are not impacted by sorting as you would expect.  I've included a very small sample of data from a table without a primary key to show what happens.FirstLast.JPGSampleFirstAndLastFunctions.accdb
0
ste5anSenior DeveloperCommented:
It's in correct, but it is about:

SELECT Last(T.ID) AS LastOfID, First(T.ID) AS FirstOfID
FROM TEST_FirstLast T
GROUP BY T.ID
ORDER BY T.ID ASC;

Open in new window


vs

SELECT Last(T.ID) AS LastOfID, First(T.ID) AS FirstOfID
FROM TEST_FirstLast T
GROUP BY T.ID
ORDER BY T.ID DESC;

Open in new window


FIRST() and LAST() as aggregates rely on the sorting of the ORDER BY. Which is per definition not part of the aggregation process.

During query execution inner ORDER BY maybe removed. This is were FIRST() and LAST() get unreliable:

Consider this is our wanted LAST()/FIRST() result;

SELECT Last(T.ID) AS LastOfID, First(T.ID) AS FirstOfID
FROM TEST_FirstLast T
GROUP BY T.ID
ORDER BY T.ID DESC

Open in new window


which we now reuse:

SELECT *
FROM (
SELECT Last(T.ID) AS LastOfID, First(T.ID) AS FirstOfID, 1 AS Dummy
FROM TEST_FirstLast T
GROUP BY T.ID
ORDER BY T.ID DESC 
) Q
ORDER BY Q.Dummy

Open in new window


Now the inner ORDER BY is not executed and the result is arbitrary. I'm getting 16 as result for LAST()/FIRST().

Thus using an explicit

SELECT TOP 1 T.ID
FROM TEST_FirstLast T
ORDER BY T.Company ASC

Open in new window


But in the end: FIRST()/LAST() have a huge potential to simply return an arbitrary value.
0
NerdsOfTechTechnology ScientistCommented:
I have Query A, with fields ColA, ColB, ColC and ColD, and sorted in ascending order of ColA
The inverse would be ORDER BY ColA DESC to get MAX(A) in the first row.

I wish to build Query B with fields ColB, ColC and the LAST (ColD) that appears in Query A.
AND:
Last Value in Column D that I am interested in, is always the one with the MAX value in Column A

Therefore, you are trying to get ColB, ColC, and ColD of the row which has max(colA). In light of this, this is the answer:

 SELECT TOP 1 ColB, ColC, ColD
 FROM table
 ORDER BY ColA DESC 

Open in new window


 This sorts the table by ColA DESC and takes top row, where MAX(ColA) AKA Last(ColD), and selects ColB, ColC, and ColD
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NerdsOfTechTechnology ScientistCommented:
Since Max(ColA) is always equal to LAST(ColD) {per OP's logic}, all that is needed is the TOP 1 SELECTion of ColB, ColC, and ColD, when ORDER BY ColA DESC is set. Split for extra research on FIRST and LAST.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.