Sql query to fetch latest record

Question

1.00/5 (2 votes)

See more:

Table

Discipline  Activity    Area    Month   Week1 TOn       Week2 TOn       Week3 TON       Week4 TON
S               A       2       June    10              20      
S               B       2       June    15                              25

I need to get the latest ton value using a query.

For eg:- For activity A/June we need the output ==10
For activity B/June we need the output ==25

Kindly provide a query to fetch the details.

What I have tried:

Using case with and but its not working kindly do the needful

Posted 21-Jun-21 18:57pm

Member 11658469

Updated 21-Jun-21 22:39pm

OriginalGriff

v2

Add a Solution

Comments

Maciej Los 22-Jun-21 2:35am

Why 10 and 25? Shoudn't be 30 and 40 accordingly?

2 solutions

Solution 1

"It's not working" is one of the most useless problem descriptions we get: it tells us absolutely nothing about the problem. We don't know if you get an error message, or the wrong data, or even that that code compiles successfully!
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
So tell us what happens when you run that code, what you expected to happen, how you checked what happened. Help us to help you!

But Google is your friend: Be nice and visit him often. He can answer questions a lot more quickly than posting them here ... 10 seconds with Google gave me solutions: SQL max of four columns - Google Search[^]
Including this: sql server - SQL MAX of multiple columns? - Stack Overflow[^]

In future, please try to do at least basic research yourself, and not waste your time or ours.

Posted 21-Jun-21 19:05pm

OriginalGriff

Comments

Member 11658469 22-Jun-21 2:11am

Col1 Col2 Col3 Col4
10 5

I need to get the output as 5 because col3 is having the last value.Kindly help to fetch the output like this.

OriginalGriff 22-Jun-21 2:41am

Follow the link(s) I gave you ...

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-21T22:39:00

Assuming you want the last non-null column from week 1 to week 4, use the COALESCE[^] function:

SQL

SELECT
    Discipline,
    Activity,
    Area,
    Month,
    Coalesce([Week4 TON], [Week3 TON], [Week2 TOn], [Week1 TOn]) As WeekTON
FROM
    YourTable
;

NB: You should avoid using spaces and other special characters in table and column names. Since your column names include spaces, you need to enclose then in square brackets everywhere you use them.