Order and group in SQL

order by and group by in same query
group by having sql
sql group by sum
group by order by, having
group by sql
order by in sql
sql group by multiple columns
how to use group by after order by in mysql

I have a database as:

Serial no.  Name
1            A
1            B
2            Aa
2            Z
3            D
3            A
3            C

and I want the result as

Serial no.  Name
2            Aa
2            Z
1            A
1            B
3            D
3            A
3            C

I want each group (having same serial no.) to be sorted with other groups on the basis of the first name in the groups but inside the groups the order of name should remain unchanged.

I have tried doing

Select * From tablename
group by `Serial no.` 
order by `Name`

but I am getting :

Serial no.  Name
2            Aa
1            A
3            D

This is the data from table:

SELECT * FROM #temp

Select *
from tablename
Order by Serial no DESC, Name;

This is the output that I obtained for following query:

What is the Difference between ORDER and GROUP BY , In all versions of MySQL, simply alias the aggregate in the SELECT list, and order by the alias: SELECT COUNT(id) AS theCount, `Tag` from  To summarize, the key difference between order by and group by is: ORDER BY is used to sort a result by a list of columns or expressions. GROUP BY is used to create unique combinations of a list of columns that can be used to form summaries. A byproduct of this operation is that the grouping tend to be sorted; however, this isn’t a guarantee.

Grouping on your table doesn't make sense at all. Order by would do just fine.

select Serialno,Name
from table _name
order by Name desc

SQL GROUP BY Statement, Group By in SQL is used to arrange similar data into group and Order By in SQL is is used to sort the data in the ascending or descending order  Group By in SQL is used to arrange similar data into group and Order By in SQL is is used to sort the data in the ascending or descending order. Wish to crack SQL job interviews? Intellipaat’s Top SQL Interview Questions are meant only for you! Order By in SQL. SQL Order By is used to sort the data in the ascending or descending order. It sorts the data in ascending order by default.

It is impossible to do what you want with SQL.

You want the order within each serial number group to be the same as in the table, but there is no well defined order within a table.

A table in SQL is an unordered set of rows, and any order must be imposed by an ORDER BY clause. So unless your order can be expressed in SQL and does not depend on the physical placement of the rows in the table, your problem cannot be solved.

The order of rows in a table can change and is implementation dependent. For example, a reorganization might change it. In PostgreSQL, for example, a simple update changes the order, and you are not even guaranteed to get the same order in two sequential scans.

SQL Group By with an Order By, What is difference between orderby and group by in SQL? @jarlh In versions 5.6 and newer, strict sql mode must be turned off in order for things like non-aggregate group by and zero dates to be permitted. – T Gray Oct 18 '16 at 21:36 @TGray, are you talking about MySQL here?

Order By and Group By in SQL - SQL Tutorial, BY clause is used in the SELECT statement . Optionally it is used in conjunction with aggregate functions to produce summary reports from the database. That's what it does, summarizing data from the database. "Group By" and "Order By" can be used together in SQL as shown in the example: SELECT deptno, sum (sal + comm) FROM emp GROUP BY deptno ORDER BY deptno; This will list the total cost of salary and commission for each department with the list sorted by department number.

MySQL GROUP BY and HAVING Clause with Examples, SQL defaults with ascending order. You would just implement it like so: ORDER BY username DESC. GROUP BY SQL Keyword. Example. SELECT * FROM  SQL max() with group by and order by To get data of 'cust_city', 'cust_country' and maximum 'outstanding_amt' from the customer table with the following conditions - 1. the combination of 'cust_country' and 'cust_city' should make a group, 2. the group should be arranged in alphabetical order,

SQL, Use the ORDER BY clause to display the output table of a query in either ascending or descending alphabetical order. Whereas the GROUP BY clause gathers  In this page, we are going to discuss the usage of GROUP BY and ORDER BY along with the SQL COUNT() function. The GROUP BY makes the result set in summary rows by the value of one or more columns. Each same value on the specific column will be treated as an individual group. The utility of ORDER BY clause is, to arrange the value of a column ascending or descending, whatever it may the column type is numeric or character.

Comments
  • I am actually using python based dbms but these I have tested in MySQL
  • thanks for the answer, but I don't want serial no. decreasing what I want is to sort each group ( ie. they have same serial no.) by the first name in the group.
  • Poor sample data. Add (3,'D'), (3,'A') and (4,'C'), and adjust the expected result accordingly.
  • I'm sorry I am not sure how to achieve this @AshutoshRawat. I will delete my answer.
  • @AshutoshRawat there is no aggregate function in this query so I don't think Group by can be used to achieve this.
  • It will give Aa A A B C D Z as answer
  • thanks, @laurenz but I want the order in which they are mentioned in a group(same serial no.) to remain same only that the whole group gets sorted based on the first name, so I think the description is right. See in your solution it is changing order inside the group , if i have given D, A, C in serial no 3 then it should remain D,A,C always.
  • I have edited my description hope it helps and sorry for all the confusion, it's my first time here
  • Why, is some alternate solution possible?
  • Thanks, but I have found an alternate solution. I am doing group by and order by then storing them into another table and then with the help of it getting the desired result.
  • Still, there is no guarantee that you will get the rows in the order you entered them into the database. If that's good enough, fine.