Solved

Sybase group by

Posted on 2007-11-20
6
810 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
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…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

895 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

11 Experts available now in Live!

Get 1:1 Help Now