Group / Union mysql report

I'm trying to get data out of a huge mysql table

Leader |   Email               |   Name
------------------------------------------
n          |  tt@bbb.com     |  Orla
y          |  a@bbb.com     |   Tom
n          |   p@xyz.com    |   Lary
n          |  xx@bbb.com   |  Richard
n          |  yy@bbb.com   | Iris
y          | ee@xyz.com    | valery


I want to output the list in the following format

Tom     |                |  a@bbb.com
            |  Orla       |  tt@bbb.com
            |  Richard  | xx@bbb.com
            | Iris          | yy@bbb.com
Valery |                | ee@xyz.com
            | Larry      | p@xyz.com


Tom is leader of group who have email addresses @bbb.com
Valery is leader of group  with email address @xzy.com

There are over 40,000 records

joomlaAsked:
Who is Participating?
 
vbNewbie2009Connect With a Mentor Commented:
You really need a fourth column or a substring of the email column to just pull out the domain name.  When ordering by the entire email address it simply does an alpha-sort; not alpha-sort by domain name.

Then when ordering by 'Email', 'Name2', 'Name1' it will appear as desired.

I through together a different query option that would use a case instead of Union.  They would both get you to the same end result but using the case only necessitates parsing the table recordset once as opposed to multiple times with the union
select *
from(select 
	case leader
	     when 'y' then name
	end as Leader
	,case leader
	     when 'n' then name
	end as Follower
	,email
	,SUBSTRING(email,INSTR(email,'@') + 1) as domain
     from tblName
     )tblA
order by domain, follower, leader

Open in new window

0
 
sshah254Commented:
Select * from (
select name as Name1, '' as Name2, Email from mytable where leader = 'Y' Union
select '' as Name1, name as Name2, Email from mytable where leader = 'N'
) x
order by Email, Name1, Name2

Ss
0
 
joomlaAuthor Commented:
Hi Ss
thanks for your input
unfortunately it didn't work
obviously it produced an output, but it's when I tried on a subset of 17 records it returns 34 records

i can't find an obvious link to help you intrepret the results
M
0
 
joomlaAuthor Commented:
Hi Ss,
my apolgies, there were actually 34 records in the table I was working on

The output is not quite what I want   (see below)
I tried changing the order in the syntax but it makes do diff

            |  Orla       |  tt@bbb.com
            |  Richard  | xx@bbb.com
            | Iris          | yy@bbb.com
Tom     |                |  a@bbb.com
            | Larry      | p@xyz.com
Valery |                | ee@xyz.com
0
 
joomlaAuthor Commented:
thanks for offering this solution.
my apologies for not getting back sooner.
i had been on a deadline and lost focus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.