MapInfo Pro

Welcome to the MapInfo Pro community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Here are some useful links where you can find more information:

Product Announcements  Product Documentation  Ideas Portal

Discussions

Members

Resources

Events

 View Only

  • 1.  SQL Select: Group By without nulls with hierarchy

    Posted 12-26-2017 12:07

    Hi,

    I have a list of customers with their addresses with the type of service they have. For example:

    See Attachment

    The result I would get with select * from CustTable Group By Address would be:

    See Attachment

    However, I have a set hierarchy where I want to see the most valuable ServiceType for each address based on:

    1) 5G

    2) 4G

    3) 3G

    I need my result to be:

    See Attachment

    Is this doable within MapInfo?

    Thanks,

    Kobi



  • 2.  RE: SQL Select: Group By without nulls with hierarchy

    Posted 12-26-2017 07:38
      |   view attached

    Correction: Ideally I would want my result to be See Attachment



  • 3.  RE: SQL Select: Group By without nulls with hierarchy

    Employee
    Posted 12-28-2017 02:00

    Hi Kobi,

    I don't have your exact data but from the example you mentioned, I was able to get the desired behavior mentioned in comment using below query.

     

    select ClientName, Address, Max(ServiceType) from CustTable Where ServiceType <> "" Group by Address into Temp1

    Select ClientName, Address, (Str$(COL3) + "G") "ServiceType" from Temp1 into Temp2

    Browse * from Temp2

     

     

    If you want the result to be same as what you mentioned before correction than that can be done using below query.

    Select * from CustTable where ServiceType <> "" order by ServiceType Desc into Temp1

    Select * from Temp1 group by Address Order By ClientName Into Temp2

    Browse * from Temp2

     

    Thanks

    Anshul