Group by...help

[Anonymous]

Hi,

I have the below piece of SQL in the load editor and need to group this by Code, Used and Name. Could someone please help on how I can do this? There are multiple rows with the same 'Code' but different names.

LOAD

    Code,

    Used,

    AWB

FROM [lib://BoxTop/AWB.qvd]

(qvd)

WHERE Used = 0

LEFT JOIN

LOAD

    CODE as Code,

    NAME

FROM [lib://BoxTop/Carrier.qvd]

(qvd);

Thank you in advance for any help.

[marcus_sommer]

Maybe in this way:

LOAD

    Code,

    Used,

    AWB

FROM [lib://BoxTop/AWB.qvd]

(qvd)

WHERE Used = 0

LEFT JOIN

LOAD

    CODE as Code,

    concat(NAME, ', ') as NAME

FROM [lib://BoxTop/Carrier.qvd]

(qvd) group by CODE;

- Marcus

[Anonymous]

Hi Marcus,

thanks for the response.

I actually want to only return one name instead of a concat on all names.

[sunny_talwar]

Which name would you want to show from the list of names that are there? Is there a way to decide this or is this completely random?

[jerryyang756]

Try This

Table1:

LOAD

    Code,

    Used,

    AWB

FROM [lib://BoxTop/AWB.qvd](qvd)

WHERE Used = 0

LEFT JOIN

LOAD

    CODE as Code,

    NAME

FROM [lib://BoxTop/Carrier.qvd](qvd);

Table2:

NoConcatenate

Load

   Code,

    Used,

    AWB,

    NAME,

    Count(NAME) as NameCount

Resident Table1 Group By Code,Used,AWB,NAME;

Drop Table Table1;

[Anonymous]

Hi Pritam,

Thanks for your response.

I still get more than one row for the name. I would like to group and show only one name, doesn't matter which one.

[Anonymous]

Hi Sunny,

Can be any name in the group code as long as it only shows one.

[sunny_talwar · Accepted solution]

Make this change to Marcus's code

LOAD

    Code,

    Used,

    AWB

FROM [lib://BoxTop/AWB.qvd]

(qvd)

WHERE Used = 0

LEFT JOIN

LOAD

    CODE as Code,

    FirstValue(NAME) as NAME

FROM [lib://BoxTop/Carrier.qvd]

(qvd) group by CODE;

[Anonymous]

THANKS! it worked