is order by and group by the same?

[ohgosh]

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.

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!

[chrisbliss18]

"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.

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.

SELECT * FROM Location ORDER BY state, city, name

This query would return the data in the following order:

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.

SELECT * FROM Location GROUP BY state

This query would return the data in the following order:

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.

SELECT state FROM Location GROUP BY state

This query would return the data in the following order:

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

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

SELECT state FROM Location GROUP BY state ORDER BY state

This query would return the data in the following order:

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

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

SELECT state, COUNT(*) AS numPeople FROM Location GROUP BY state ORDER BY state

This query would return the data in the following order:

state     | numPeople
----------|------------
Kansas    | 2
Oklahoma  | 3
Texas     | 1

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

[blacklocist]

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:

[Vote4WillFree]

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.

[guruparthi]

Order by and group by are totally different.

Order by

It is used to sort the table values ascending and descending order

for ex:

select * from table_name order id desc

where, fields are sorts descending order

Group by

It is used to display distinct values on table. It eliminate duplicate values from table.

for ex:

select * from table_name group by name

where,
Suppose tha table've same name repeatly. The you can eliminate those values using group by.