Solved

Sybase group by

Posted on 2007-11-20
6
807 Views
Last Modified: 2012-06-21
I have to join two tables on a column name
1) table has

Name      Group      Account      
John      1      1      
John      1      2      
Harry      1      3      
Smith      2      4      

table 2)
Name      Address
John      NJ
John      NY
Harry      CA
Smith      MO

and group similar groups together. If I use group by it doesn't provide right results. Please advise.
0
Comment
Question by:saibsk
  • 3
  • 2
6 Comments
 
LVL 39

Expert Comment

by:Adam314
ID: 20323519
What results do you want?
0
 

Author Comment

by:saibsk
ID: 20323542

Name      Group      Account      Address
John      1      1      NJ
John      1      2      NY
Harry      1      3      CA
Smith      2      4      MO
0
 

Author Comment

by:saibsk
ID: 20323552
I want to write to a file in this format. I want to group all the accounts of same group number together when I write to the file. I cannot use group by when I write the query. It is providing confusing results. I am using another table to join for generating the above result.

#Group 1 Accounts
John, 1, 1, NJ, 2, NY
Harry, 1, 3, CA

#Group 2 accounts
Smith, 2, 4, MO      

I could write it to the file but I want to write similar groups together something like above.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 39

Expert Comment

by:Adam314
ID: 20323598
How do you know John with Account 1 is the NJ address an not the NY address?

To get the results ordered by group, use
    ORDER BY Group
0
 
LVL 19

Accepted Solution

by:
grant300 earned 500 total points
ID: 20323729
The GROUP BY is doing exactly what it is supposed to do.  The problem is that a GROUP BY, while necessary, is not adequate by itself.  You are doing more than that; you are also doing a pivot to stack multiple records with the same NAME in the same row.

The bad news is that this not supported directly in SQL.  No way in a single query.

The good news is that, within certain constraints, there are tricks you can do to get something like the result you want.

Given that you are trying to do this from Perl, I would recommend you get the raw query results you need from the database and do the funky formatting in Perl.  Break processing and stacking string values is probably easier for you to do there than it would be for us to introduce a bunch of complex SQL and stored procedure constructs.

The SQL gets pretty simple; no group by required...

SELECT A.NAME, A.GROUP, A.ACCOUNT, B.ADDRESS
   FROM <TABLE A> A
    JOIN <TABLE B> B
       ON B.NAME = A.NAME
 ORDER BY A.GROUP, A.NAME, A.ACCOUNT

Then in Perl, you are going to take these results and do your report writing.
 - Keep track of the last Group and Name values
 - Every time the Group changes, spit out a Group Header
 - Every record that comes in with the same Name, add the Account and Address fields to the output record string
 - Every time the Name changes, spit out the output record string, clear it, and drop in the new Name, Group, Account, and Address field.
 - When you run our of records, spit out the output record string

That ought to do it for you with no psychedelic SQL and no stored procedures.

Regards,
Bill
0
 

Author Closing Comment

by:saibsk
ID: 31410232
Thanks. Works great.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now