Re: Need Help with SQL

[Vinay Gavankar <vinaygav@xxxxxxxxx>]

Correct.

On Wed, May 5, 2021 at 8:35 AM Therrien, Paul via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

> I don't have an answer.... just trying to decipher the requirement:
>
> You are asking for a result set that shows for any set of values for A, B,
> & C; show the value in Column 4 of the value that occurs most often in
> column 4? And you want distinct A, B, & C values so that you only see, as
> in your example, either A1, B1, C1, D2 or A1, B1, C1, D4, but not both.
>
> Data:
> Frog, Giraffe, Lion, Ocelot
> Frog, Giraffe, Lion, Cat
> Frog, Giraffe, Lion, Dog
> Frog, Giraffe, Lion, Cat
> Frog, Giraffe, Lion, Ocelot
> Tiger, Giraffe, Lion, Owl
> Tiger, Giraffe, Lion, Owl
> Frog, Bison, Lion, Ocelot
> Frog, Bison, Lion, Armadillo
> Frog, Bison, Lion, Cat
>
> Result would be:
> Either one of these:
>         Frog, Giraffe, Lion, Cat
>         Frog, Giraffe, Lion, Ocelot
> (You would not want to see:  Frog, Giraffe, Lion, Dog)
>
> This one:
>         Tiger, Giraffe, Lion, Owl
>
> Either one of these three:
>         Frog, Bison, Lion, Ocelot
>         Frog, Bison, Lion, Armadillo
>         Frog, Bison, Lion, Cat
>
> -----Original Message-----
> From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
> Of Vinay Gavankar
> Sent: Tuesday, May 4, 2021 6:57 PM
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: Need Help with SQL
>
> Hi,
> I need some help with sql.
> My table has 4 fields: A, B, C, D
> I need my output to be grouped by A, B, C and a value of D which occurs
> maximum number of times (if more than one value occurs same number of
> times, any of those values is fine).
> So if my records are:
> A1, B1, C1, D1
> A1, B1, C1, D2
> A1, B1, C1, D2
> A1, B1, C1, D3
> A1, B1, C1, D4
> A1, B1, C1, D4
> A2, B1, C2, D1
> My output should be:
> A1, B1, C1, D2  or it can be A1, B1, C1, D4
> A2, B1, C2, D1
>
> This would be a one time process on about a million records so performance
> is not an issue and it need not be a single sql.
>
> TIA
> Vinay
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com