Click here to Skip to main content
15,031,495 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
This is my table:

id | date | total
1 |2021.01| 1
1 |2021.02| 3 <--- this is max value
1 |2021.03| 2 <--- this is last value

That is true, 'id' field is exactly what it is, - this table is a product of group by.

I am trying to select now 2 values: the last record and the record with highest value, the result should be like this:

lastValue | maxValue
2 | 3

What I have tried:

I have tried some crazy stuff, but frankly speaking I have zero idea how this is achieved in SQL. Any help is greatly appreciated.

EDIT: 1 table scan allowed.
Posted
Updated 22-Jun-21 5:16am
v2
Comments
SeeSharp2 22-Jun-21 9:41am
   
Sounds like all you need is
SELECT TOP 2 *
FROM table
ORDER BY id_field DESC

THat will give you the last 2 records that were inserted.
csrss 22-Jun-21 9:42am
   
Thsi table here is just an example. Actual table has more data. And return format is very important.
SeeSharp2 22-Jun-21 11:32am
   
Then change what fields you pull. Very easy.

Seems simple enough:
SQL
SELECT
    (SELECT TOP 1 total FROM yourTable ORDER BY date DESC) As lastValue,
    (SELECT Max(total) FROM yourTable) As maxValue
;

EDIT: Option 2 - requires SQL Server 2016 or later:
SQL
SELECT TOP 1
    LAST_VALUE(total) OVER (ORDER BY date DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) As lastValue,
    MAX(total) OVER() As maxValue
FROM
    yourTable
;
LAST_VALUE (Transact-SQL) - SQL Server | Microsoft Docs[^]

EDIT: Option 3:
SQL
WITH cte As
(
    SELECT
        total,
        ROW_NUMBER() OVER (ORDER BY date DESC) As drn
    FROM
        yourTable
)
SELECT
    MAX(CASE drn WHEN 1 THEN total END) As lastValue,
    MAX(total) As maxValue
FROM
    cte
;
   
v4
Comments
csrss 22-Jun-21 11:13am
   
1 table scan allowed.
Richard Deeming 22-Jun-21 11:15am
   
So this is a homework assignment or an interview test?

Otherwise, why the arbitrary restriction? And why not mention that restriction in your question?
csrss 22-Jun-21 11:17am
   
Updated question. Yes, forgot about it. No, it's for me. It is not a table what I am scanning. It is already a result of a query, so I cannot run it multiple times, or insert stuff in temp table - production result contain millions of records.
Richard Deeming 22-Jun-21 11:27am
   
You could try the LAST_VALUE function, but it's unlikely to produce a "single table scan". If you look at the execution plan, you'll probably end up with multiple table spools and joins.
csrss 22-Jun-21 11:33am
   
Thanks. This is something what I need. I have tested first_value - is there any difference between them?
Richard Deeming 22-Jun-21 11:37am
   
No difference - so long as you change the sort order, you should get the same values and execution plan for FIRST_VALUE and LAST_VALUE.

You could also try the ROW_NUMBER alternative. On my simple test data, it's marginally more expensive than the LAST_VALUE query, but the execution plan is simpler. You'd need to compare the performance on your data to see which is better.
csrss 22-Jun-21 11:46am
   
Thanks very much!
Group them:
SQL
SELECT MAX([Date]) AS [Date], 
       MAX(Total) AS Total
FROM MyTable
GROUP BY ID
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900