mysq nested query

I have a query

Select name,address,product from clients

For my own education how would a nested query be:

select (Select name,address,product from clients) from ????
robrodpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PooleCommented:
Select name,address,product,(Select Top 1 proddesc from products P where P.product=C.product) as productdescription  from clients C

Open in new window


This is an example of one where you are selecting in this example a product description from the product in your clients table
0
robrodpAuthor Commented:
My problem is I have a query with group by and with rollup and cannot set an order by

I can do a 2 step process: cteate  a new table with the original query and perform the order by on the resulting table.

But I would like to have a single query
0
Randy PooleCommented:
I would need to know specifics on the tables and what exactly your trying to do
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

robrodpAuthor Commented:
Very simple

select name,address,product from clients group by with rollup

With rollup I cannot use order by soI am creating a temporary table that I can than order by

like

create table temp as select name,address,product from clients group by with rollup


then I order by name.. and export to a csv value. It works

But I want to have a single query (not haveing to go throuch the temporary file) that I can be exported
0
PortletPaulfreelancerCommented:
this makes no sense

"select name,address,product from clients group by with rollup"

what are you aggregating? (or have you just excluded this to 'simplify'?)
It is not true that you cannot use order by with group by ... with rollup

A small sample of your existing output, and the desired result (sorted) would really help solve this question.
0
robrodpAuthor Commented:
Say  Group by product  order by client
0
PortletPaulfreelancerCommented:
"My problem is I have a query with group by and with rollup and cannot set an order by"

No problem, I'll write that query when you provide the sample data and expected result. It's "Very simple".

While I wait for the data perhaps you would like to read this:
When you use ROLLUP, you cannot also use an ORDER BY clause to sort the results. In other words, ROLLUP and ORDER BY are mutually exclusive. However, you still have some control over sort order. GROUP BY in MySQL sorts results, and you can use explicit ASC and DESC keywords with columns named in the GROUP BY list to specify sort order for individual columns. (The higher-level summary rows added by ROLLUP still appear after the rows from which they are calculated, regardless of the sort order.)
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

and if using GROUP BY to control the desired result isn't sufficient there might still be other possibilities.

I cannot stress enough the importance of you providing these 2 things:

1. Sample Data

Not a query result, data for the tables involved.

2. Expected Result

Here it is necessary so you demonstrate what the sort should be.I/we really cannot do this on your behalf because we are not the ones having this need to solve.
0
Randy PooleCommented:
WIth group by and rollup in mysql you can not use an order by clause, you can however append asc or desc on each of the group by columns.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
:)
"should not" is a better description I think

I'm still waiting for sample data and expected result.

but for debate:
note the following, It implements a method that would facilitate an order after a group with rollup (hard to follow however) and I would NOT recommend it unless there was a really good reason
select
        @row_num := @row_num + 1 as rn
      , @grp_num := IF(@prev_value = coalesce(country, year) ,@grp_num + 1, 1) as gn
      , year
      , country
      , product
      , profit
      , @prev_value := coalesce(country, year) as pv
from (
  
      SELECT
              year
            , country
            , product
            , SUM(profit) profit
      FROM sales
      GROUP BY
              year ASC
            , country ASC
            , product ASC
            WITH ROLLUP
    ) grpqry
CROSS JOIN(SELECT @grp_num :=0, @row_num :=0, @prev_value :='') vars
order by
   year, country, product, rn

http://sqlfiddle.com/#!9/4fd4e6/16

Open in new window

0
Randy PooleCommented:
Yeah but that is cheating not a standard select with it :P Inside your from Select you could not add an order by :) but actually I think what you have there is what he is wanting.
0
robrodpAuthor Commented:
The asc or desc on the group by does the trick
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

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.