Link to home
Start Free TrialLog in
Avatar of Member_2_7966563
Member_2_7966563

asked on

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).
Avatar of ste5an
ste5an
Flag of Germany image

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.
Pls provide input data and expected output.

First and last are available in sql 2012+
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.
SOLUTION
Avatar of Máté Farkas
Máté Farkas
Flag of Hungary image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Member_2_7966563
Member_2_7966563

ASKER

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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.