Solved

MySQL Query Help

Posted on 2011-09-27
8
153 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 82

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 82

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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 82

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 82

Accepted Solution

by:
Dave Baldwin earned 500 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

The task A number given should be formatted for easy reading by separating digits into triads. Format must be made inline via JavaScript, i.e., frameworks / functions are not welcome. So let’s take a number like this “12345678.91¿ and format i…
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now