Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL Query Help

Posted on 2011-09-27
8
Medium Priority
?
163 Views
Last Modified: 2012-06-21
I have a table with columns blade_id, class_id, dateReleased_yr . I am trying to run a query which will give me results order by dateReleased_yr desc . This works fine but next step is to group all the blade_id with the same class_id. I tried different combination of order by but didnt work.

//if I just to the dateReleased_yr sort I get

blade_id  class_id  dateReleased_yr
1            2            2011
2            3            2010
3            2            2010
4            4            2009
//What I want to get is group all the blade_id with same class_id togather with the desc year
blade_id  class_id  dateReleased_yr
1            2            2011
3            2            2010
2            3            2010
4            4            2009

Open in new window

0
Comment
Question by:PranjalShah
  • 4
  • 3
8 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 36712599
SELECT * FROM table ORDER BY class_id ASC, dateReleased_yr DESC
0
 
LVL 8

Author Comment

by:PranjalShah
ID: 36712684
If I do the above query it keeps the order of the class_id and then sort with dateReleased_yr. What I need is to show the blade_id of the same class_id togather. So if a blade_id 4 with class_id 5 is at the top then all the blade_ids with the same class_id will group togather at the top even though these blade_ids have lower dateReleased_yr than other ones.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 36713508
With the data you showed above, my query produces the exact results you asked for.  In phpmyadmin, I created a test table, entered the data, and ran the query.  I don't understand what else you want.  Post a more complete example of what you want.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 2

Expert Comment

by:maricksville
ID: 36713557
@Pranjal

I agree with Dave on this, the query he has posted is correct if I'm reading your request correctly. If you are after the mechanism that displays the blade_id this can be output after the above query has been executed.

For example, from Daves query, and using PHP:

$query = mysql_query("SELECT blade_id, class_id, dateReleased_yr FROM table ORDER BY class_id ASC, dateReleased_yr DESC ");
while($row = mysql_fetch_array($query);
{
// Output the results
echo $row['blade_id']." ".$row['class_id']." ".$row['dateReleased_yr'];
}

Open in new window


In PHP that script will show the Blade ID, the Class ID and the Date Released in the order of Class ID first and then Date Released. If you're using another language then the syntax naturally would be different.
0
 
LVL 8

Author Comment

by:PranjalShah
ID: 36713560
Attached is the row table data. What I am trying to get from this is, sort the data desc by dateReleased_yr and doing that say I get blade_id 286 on the top. blade_id 286 has a class_id 45 and there could be many blade_ids with the same class_id. I want all the blade_ids with this class_id to show on the top regardless of dateReleased_yr. So say a blade_id 300 has a dateRelealesed_yr of 2007 but it belongs to the same class_id as blade_id 286, I want to show those blades on the top.
blade_id class_id  dateReleased_yr
3	1	2008
4	1	2008
5	1	2008
6	2	2010
68	14	2010
77	109	2009
81	109	2009
99	38	2007
100	38	2007
113	14	2003
114	14	2009
116	14	2003
119	15	2009
120	15	2009
121	15	2009
140	6	2006
144	38	2010
286	45	2012
287	45	2012
288	38	2007
299	1	2010
300	1	2010
306	130	2009
308	130	2009
309	86	2008
310	86	2008
323	89	2007
324	89	2007
325	89	2007
326	89	2007
327	89	2007
342	1	2010
354	5	2003
362	90	2010
363	90	2010
364	85	2010
444	6	2010
604	81	2011
605	81	2011
606	81	2011
608	91	2011
618	81	2011
621	107	2011
626	107	2011
659	125	2009
779	151	2011
799	185	2012
800	185	2011
801	185	2011
806	190	2011
807	190	2011
823	194	2011
824	194	2011
825	195	2011
826	195	2011
827	196	2011
828	196	2011
829	196	2011
830	196	2011
831	196	2011
832	196	2011
833	196	2011
834	196	2011
835	196	2011
836	196	2011
839	90	2011
945	207	2012
946	207	2012

Open in new window

0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 36713587
That is still confusing to me.  Try this version:

SELECT * FROM table ORDER BY dateReleased_yr DESC, class_id ASC
0
 
LVL 8

Author Comment

by:PranjalShah
ID: 36713617
From the above query, I get the data sorted by date fine. What I want is, group all the blades from the same class_ids (the class_id which we get when sorting by date) . So running above query, I get blade_id 799 at number 3 which has class_id 185. There are some data with the same class_id with lower dates. I need all those blades to show up after the blade_id 799 so that I get the grouping of class_ids as well. This way I have a blade_id with latest date and all the blade from the same class togather.
0
 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 2000 total points
ID: 36713657
Ok, you have a limited number of choices.  In the ORDER BY section, you put the fields in the order that you want them sorted.  Put the most important first, second most important second, and third most important third.

SELECT * FROM table ORDER BY dateReleased_yr DESC, class_id ASC, blade_id ASC

You have only three fields.  Put them in the order that gets you the results that you want.  That is the only thing you can do.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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
Course of the Month10 days, 23 hours left to enroll

886 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