Improve company productivity with a Business Account.Sign Up

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

MySQL Query Help

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
PranjalShah
Asked:
PranjalShah
  • 4
  • 3
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
SELECT * FROM table ORDER BY class_id ASC, dateReleased_yr DESC
0
 
PranjalShahAuthor Commented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
maricksvilleCommented:
@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
 
PranjalShahAuthor Commented:
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
 
Dave BaldwinFixer of ProblemsCommented:
That is still confusing to me.  Try this version:

SELECT * FROM table ORDER BY dateReleased_yr DESC, class_id ASC
0
 
PranjalShahAuthor Commented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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