How to write a query in SQL to display max number of column's

Question

0.00/5 (No votes)

See more:

I have two tables like DataHeader and DataDetail
i want to display the Remarks from DataDetail table which are having high DDid
and if remarks not available in DataDetail then I want to display Remarks from DataHeader Table

how to write query in SQL

DataHeader
DHid A B C Remarks
1 abc 123 xyz DHidR1
2 bcd 252 jhyvb DHidR2
3 efg 541 sdh DHidR3

DataDetail
DHid DDid Remarks
1 1 working
1 2 working
1 3 worked
2 1 Remarks1
2 2 Remarks2
2 3 Remarks3
2 4 Remarks4
2 5 Remarks5

i want to display the data like
DHid A B C Remarks
1 abc 123 xyz worked
2 bcd 252 jhyvb Remarks5
3 efg 541 sdh DHidR3

Sry I Cant write tables format

What I have tried:

i have tried left outer join to Dataheader and i displaying dataheader remarks only

Posted 20-Mar-18 4:47am

Parazival

Updated 20-Mar-18 4:53am

Add a Solution

1 solution

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 · 2018-03-20T04:54:00

Something like this:

SQL

SELECT
    DH.DHid,
    DH.A,
    DH.B,
    DH.C,
    IsNull(
        (
            SELECT TOP 1 DD.Remarks
            FROM DataDetail As DD
            WHERE DD.DHid = DH.DHid
            And DD.Remarks Is Not Null
            ORDER BY DD.DDid DESC
        ),
        DH.Remarks
    ) As Remarks
FROM
    DataHeader As DH
;

Or:

SQL

WITH cteDetail As
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY DHid ORDER BY DDid DESC) As RN,
        DHid,
        Remarks
    FROM
        DataDetail
    WHERE
        Remarks Is Not Null
)
SELECT
    DH.DHid,
    DH.A,
    DH.B,
    DH.C,
    IsNull(DD.Remarks, DH.Remarks) As Remarks
FROM
    DataHeader As DH
    LEFT JOIN cteDetail As DD
    ON DD.DHid = DH.DHid
    And DD.RN = 1
;