943,947 Members | Top Members by Rank

Ad:

MySQL Discussion Thread
Unsolved
Views: 28971

Sep 15th, 2005

0

is order by and group by the same?

Expand Post »

Hi there,

wanna ask if the sql syntax order by and group by the same?
eg: i have a dropdownlist and the values displayed should be in ascending order. hence is there any difference if i use these 2 sql statements below.

MySQL Syntax (Toggle Plain Text)

sql1 = "select distinct * from t_staff group by staff"
sql2 = "select distinct * from t_staff order by staff"

i've tested it out and it works well. was curious to know if there is any explaination/meaning behind the scene.

Though i know it may be peanut to you guys..but Thanks Alot!

Similar Threads

One-way(inbound)links offer in Search Engine Optimization
is order by and group by the same? in ASP
A new Enterprise Architecture online user group in IT Professionals' Lounge

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

41 posts
since Jul 2005

Sep 15th, 2005

0

Re: is order by and group by the same?

"GROUP BY" and "ORDER BY" are very different. "GROUP BY" associates all records together based on a column. "ORDER BY" sorts a alphabetically based on column.

Let's create some sample data for a table called Location.

MySQL Syntax (Toggle Plain Text)

name    | state     | city
--------|-----------|---------------
Joe     | Oklahoma  | Oklahoma City
Bob     | Texas     | Dallas
Susan   | Oklahoma  | Norman
Tom     | Kansas    | Kansas City
Carol   | Kansas    | Kansas City
Sharon  | Oklahoma  | Tulsa

Now let's execute some queries.

MySQL Syntax (Toggle Plain Text)
SELECT * FROM Location ORDER BY state, city, name
SELECT * FROM Location ORDER BY state, city, name

This query would return the data in the following order:

MySQL Syntax (Toggle Plain Text)

name    | state     | city
--------|-----------|---------------
Carol   | Kansas    | Kansas City
Tom     | Kansas    | Kansas City
Susan   | Oklahoma  | Norman
Joe     | Oklahoma  | Oklahoma City
Sharon  | Oklahoma  | Tulsa
Bob     | Texas     | Dallas

Notice that the data is ordered alphabetically first by state then by city and then by name.

MySQL Syntax (Toggle Plain Text)
SELECT * FROM Location GROUP BY state
SELECT * FROM Location GROUP BY state

This query would return the data in the following order:

MySQL Syntax (Toggle Plain Text)

name    | state     | city
--------|-----------|---------------
Joe     | Oklahoma  | Oklahoma City
Bob     | Texas     | Dallas
Tom     | Kansas    | Kansas City

"GROUP BY" will return at most one record for each value of the specified column. In this case, the first record for each value in the state column was returned. This doesn't prove to be of much use in this case, but "GROUP BY" is very powerful when you use it to get specific types of data. Let's say we want just a list of the states that are represented in the data.

MySQL Syntax (Toggle Plain Text)
SELECT state FROM Location GROUP BY state
SELECT state FROM Location GROUP BY state

This query would return the data in the following order:

MySQL Syntax (Toggle Plain Text)

state
-----------
Oklahoma
Texas
Kansas

Now let's say that we want that list ordered. We'd combine "GROUP BY" with "ORDER BY".

MySQL Syntax (Toggle Plain Text)
SELECT state FROM Location GROUP BY state ORDER BY state
SELECT state FROM Location GROUP BY state ORDER BY state

This query would return the data in the following order:

MySQL Syntax (Toggle Plain Text)

state
-----------
Kansas
Oklahoma
Texas

"GROUP BY" helps use some of MySQL's powerful tools. Tools like COUNT work really well with "GROUP BY" queries.

MySQL Syntax (Toggle Plain Text)
SELECT state, COUNT(*) AS numPeople FROM Location GROUP BY state ORDER BY state
SELECT state, COUNT(*) AS numPeople FROM Location GROUP BY state ORDER BY state

This query would return the data in the following order:

MySQL Syntax (Toggle Plain Text)
state     | numPeople
----------|------------
Kansas    | 2
Oklahoma  | 3
Texas     | 1
state     | numPeople
----------|------------
Kansas    | 2
Oklahoma  | 3
Texas     | 1

I think I went a bit overboard, but I hope I helped your understanding.

Reputation Points: 38

Solved Threads: 25

Posting Shark

Offline

902 posts
since Aug 2005

Jul 11th, 2006

0

Re: is order by and group by the same?

Quote originally posted by chrisbliss18 ...

I think I went a bit overboard, but I hope I helped your understanding.

Ahh no, you can never go overboard on explaining stuff. Nice work! :cheesy:

Reputation Points: 10

Solved Threads: 2

Junior Poster in Training

Offline

87 posts
since Apr 2006

Jul 11th, 2006

0

Re: is order by and group by the same?

No they are different.

Order by helps to sort the information by a certain column. By default is sorts ascending in mysql, however you can force it to sort descending by placing the keyword DESC after the column(s) you want to order by.

Group by helps to group information depending on the columns and perform various functions. For example, if you have 4 records that look like this:

table1

name quantity price
Sam 1 2.00
Sam 2 4.00
John 3 2.00
John 1 4.00

And you run the following sql statement:

SELECT * FROM table1 GROUP BY name

You'll get the result:

Sam 1 2.00
John 1 4.00

You can also modify the original Sql statement and get a better result:

SELECT name,sum(quantity),sum(price) FROM table1 GROUP BY name

will get you

Sam 3 6
John 4 6

Hope it helps.

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Jul 2006

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Updating but bypassign key constraints

Next Thread in MySQL Forum Timeline: MySQL insert statement

About Us | Contact Us | Advertise | Acceptable Use Policy

Forum Index | Build Custom RSS Feed

© 2011 DaniWeb® LLC