Sql: select top and max

Question

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 22-Jun-21 3:20am

csrss

Updated 22-Jun-21 5:16am

v2

Add a Solution

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.

2 solutions

Solution 1

Group them:

SQL

SELECT MAX([Date]) AS [Date], 
       MAX(Total) AS Total
FROM MyTable
GROUP BY ID

Posted 22-Jun-21 5:11am

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2021-06-22T05:11:00

Solution 2

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
;

Posted 22-Jun-21 5:11am

Richard Deeming

Updated 22-Jun-21 5:39am

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!