•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 423,631 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,207 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser: Programming Forums
Views: 15286 | Replies: 3
 |
•
•
Join Date: Jul 2005
Posts: 41
Reputation: 
Rep Power: 4
Solved Threads: 0
Light Poster
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.
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!
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!
cheers,
ohgosh
ohgosh
•
•
Join Date: Aug 2005
Location: Oklahoma
Posts: 902
Reputation:
Rep Power: 5
Solved Threads: 21
Posting Shark
"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.
Now let's execute some queries. This query would return the data in the following order: Notice that the data is ordered alphabetically first by state then by city and then by name. This query would return the data in the following order: "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. This query would return the data in the following order: Now let's say that we want that list ordered. We'd combine "GROUP BY" with "ORDER BY". This query would return the data in the following order: "GROUP BY" helps use some of MySQL's powerful tools. Tools like COUNT work really well with "GROUP BY" queries. This query would return the data in the following order:
I think I went a bit overboard, but I hope I helped your understanding.
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
SELECT * FROM Location ORDER BY state, city, name
name | state | city --------|-----------|--------------- Carol | Kansas | Kansas City Tom | Kansas | Kansas City Susan | Oklahoma | Norman Joe | Oklahoma | Oklahoma City Sharon | Oklahoma | Tulsa Bob | Texas | Dallas
SELECT * FROM Location GROUP BY state
name | state | city --------|-----------|--------------- Joe | Oklahoma | Oklahoma City Bob | Texas | Dallas Tom | Kansas | Kansas City
SELECT state FROM Location GROUP BY state
state ----------- Oklahoma Texas Kansas
SELECT state FROM Location GROUP BY state ORDER BY state
state ----------- Kansas Oklahoma Texas
SELECT state, COUNT(*) AS numPeople FROM Location GROUP BY state ORDER BY state
state | numPeople ----------|------------ Kansas | 2 Oklahoma | 3 Texas | 1
Did we help you? Did we miss the point entirely? Update your thread and let us know.
Don't like the answers you are getting?
Did you try searching?
Clean up and optimize Windows 2000/XP
Don't like the answers you are getting?
Did you try searching?
Clean up and optimize Windows 2000/XP
•
•
Join Date: Apr 2006
Posts: 87
Reputation:
Rep Power: 3
Solved Threads: 2
Junior Poster in Training
•
•
•
•
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:
•
•
Join Date: Jul 2006
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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.
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.
|
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- One-way(inbound)links offer (Search Engine Optimization)
- is order by and group by the same? (ASP)
- A new Enterprise Architecture online user group (Web Developers' Lounge)
Other Threads in the MySQL Forum
- Previous Thread: Updating but bypassign key constraints
- Next Thread: MySQL insert statement
