is order by and group by the same?
 

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!

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.

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.

Re: is order by and group by the same?
 

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

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.