Whats the different between order by and group by ?

Question

1.00/5 (2 votes)

See more:

I'm learning THE SQL Function , so i try these statments :

SELECT `umeta_id` FROM `wp_usermeta` where umeta_id < 7 order by meta_key
and the output was :

6
4
2
3
1
5

and i also try :

SELECT `umeta_id` FROM `wp_usermeta` where umeta_id < 7 group by meta_key
and the output was :
6
4
2
3
1
5

so what is the different between these two functions ? and when i should use group by or order by ?

What I have tried:

what i write above in the two statement's .

Posted 14-May-17 21:37pm

Demon413

Updated 15-May-17 2:53am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CPallini · Answer 1 · 2017-05-14T21:52:00

Solution 1

Well, ORDER orders while GROUP, ... groups!

Try, for instance:

SQL

CREATE TABLE PERSON(
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL
);

INSERT INTO PERSON VALUES ('JOHN', 40);
INSERT INTO PERSON VALUES ('JACK', 20);
INSERT INTO PERSON VALUES ('JIM', 40);
INSERT INTO PERSON VALUES ('JANE', 20);

and then the differnece between:

SQL

SELECT AGE FROM PERSON ORDER BY AGE;

and

SQL

SELECT AGE FROM PERSON GROUP BY AGE;

Posted 14-May-17 21:52pm

CPallini

Comments

Demon413 15-May-17 4:46am

not good answer actually , and still dont understand ....

CPallini 15-May-17 5:15am

The fact that you are still unable to understand doens't automatically classify the answer as bad :P
I suggest you to read a SQL tutorial.

CHill60 · Answer 2 · 2017-05-15T02:53:00

Solution 1 is actually a very good example, but you would have to run the SQL to see that.

Your question should have been tagged MySQL as if you run your second query with MS Sql Server you will get an error message

Quote:
Column 'wp_usermeta.umeta_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

That might actually give you a hint as to the difference.

ORDER BY changes the order in which the rows are returned. It SORTS the data based on the column name you give it.

GROUP BY groups the data together - it aggregates (groups) information. You usually see it used with functions such as SUM, COUNT, AVG etc.

Using the same table as Solution 1, this query

SQL

SELECT [NAME],AGE FROM PERSON ORDER BY AGE;

gives the results

JACK	20
JANE	20
JOHN	40
JIM	40

20 comes before 40 so all of the rows with an Age of 20 are listed first, followed by all the rows with an Age of 40.

This query

SQL

SELECT AGE, COUNT([NAME]) FROM PERSON GROUP BY AGE;

gives the results

20	2
40	2

In other words there are 2 rows that have Age = 20 and 2 rows that have Age = 40