Group / Union mysql report

Posted on 2011-05-04
Medium Priority
Last Modified: 2012-05-11
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

Question by:joomla
  • 3

Expert Comment

ID: 35695375
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


Author Comment

ID: 35695438
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

Author Comment

ID: 35695485
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

Accepted Solution

vbNewbie2009 earned 500 total points
ID: 35696479
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 *
	case leader
	     when 'y' then name
	end as Leader
	,case leader
	     when 'n' then name
	end as Follower
	,SUBSTRING(email,INSTR(email,'@') + 1) as domain
     from tblName
order by domain, follower, leader

Open in new window


Author Closing Comment

ID: 35919173
thanks for offering this solution.
my apologies for not getting back sooner.
i had been on a deadline and lost focus

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

839 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