Solved

mysql procedure order by

Posted on 2010-08-23
14
372 Views
Last Modified: 2012-06-27
Hi experts,
I have an urgent problem. i have  large table. I am trying to extract rows by a column named category and sort them by a numerical column in ascending order. I then want to insert these rows into  another table.
So I am using a procedure which fetches the set of category names and get rows for that cateogry and sorts them.
However the order by does not seem to work. It does not work in other in stored procedures.
I need to solve this right away. I would really appreciate any help on this
thanks
0
Comment
Question by:guyneo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 2
14 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 33503896
last I checked, you can't do:
insert into table1 (
   select * from table2 order by column1)

sql complains about the order by. There may be a really complicated way to make sure the data gets put into the table in the right order, but it's much much simpler to just add the order by onto the query when you are getting the data out of it.
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 33503974
You dont. Just have an index on the newly created table on column1 !
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 33504180
Why would you want to physically sort the records to insert into another table? It serves no useful process at all.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Author Comment

by:guyneo
ID: 33504290
Actually I can't sort all the data in the final table by column1. It is more like order by category_column asc, numeric_column asc. Sort of like sorting by columns in excel.
@aron the query you suggest does not work. it perfectly displays when its is a normal query. However  it does not work once we add insert into like you suggested.
Anyway I exported the data into an outfile. Here is what making me go crazy. The data is the exported file is exactly what I need however when I import back into the table the sorting is gone. does it have to anything with the nature of the numerical column? its float
0
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 50 total points
ID: 33504419
ahh, google "dynamic sql" for more explanations of what I'm about to show you.

Make a stored proc called getsortedstuff() that takes a column name as it's input. Then use something like this for the code of the proc:

declare @sql varchar(8000);
set @sql = 'select * from table 1 order by ' + @sortcolumn;
exec @sql;

call it using getsortedstuff('category_column');
0
 
LVL 1

Author Comment

by:guyneo
ID: 33504457
@Neilsr.  you are right.  
I was trying to get the output anyway I can. i am  in a crunch.
My problem is:
I need to extract and output the  top 1000 winners in each category. The table has 80000 and 500 categories. Each row has name,category and score  (numeric). This is my actual goal.
Can you tell me a query that will do that.  
I would really appreciate that
0
 
LVL 1

Author Comment

by:guyneo
ID: 33504462
sorry I meant 80,000 rows
0
 
LVL 1

Author Comment

by:guyneo
ID: 33504471
and top 100 winners :) sorry
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 33504646
Ah thats easy :D
Read this article and all will become clear to you, I hope!
http://www.sqlservercurry.com/2008/09/select-top-n-rows-per-groupcategory.html 
0
 
LVL 1

Author Comment

by:guyneo
ID: 33504792
Thats seems sqlserver.  Will it work for mysql?
0
 
LVL 37

Accepted Solution

by:
Neil Russell earned 450 total points
ID: 33504908
Oops SORRY! My bad.
Heres a working example for MYSQL!

SELECT a.* FROM articles AS a
  LEFT JOIN articles AS a2  
    ON a.section = a2.section AND a.article_date <= a2.article_date
GROUP BY a.article_id
HAVING COUNT(*) <= 100;
0
 
LVL 1

Author Comment

by:guyneo
ID: 33511053
Thanks Neilsr. That looks like what I need.
I don't have a single column key. but a combination of two columns.
I will try to translate it to my needs and let you know how it went.
0
 
LVL 1

Author Comment

by:guyneo
ID: 33517280
Awesome Neilsr. Works exactly for what I need.
aarontomosky: I didnot explore what you have suggested. I will check it out too thanks
0
 
LVL 1

Author Closing Comment

by:guyneo
ID: 33517310
Thanks guys for being so responsive
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

635 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