[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 112
  • Last Modified:

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 ????
0
robrodp
Asked:
robrodp
  • 4
  • 4
  • 3
2 Solutions
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
"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
 
PortletPaulCommented:
:)
"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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now