Algebra Relational sql GROUP BY SORT BY ORDER BY

sql group-by relational-database sql-order-by relational-algebra
Cyberflow picture Cyberflow · Feb 23, 2015 · Viewed 38.5k times · Source

I wanted to know what is the equivalent in GROUP BY, SORT BY and ORDER BY in algebra relational ?

Answer

Simo Kivistö picture Simo Kivistö · Feb 23, 2015

Neither is possible in relational algebra but people have been creating some "extensions" for these operations (Note: in the original text, part of the text is written as subscript).

GROUP BY, According to the book Fundamentals of Database Systems (Elmasri, Navathe 2011 6th ed):

Another type of request that cannot be expressed in the basic relational algebra is to specify mathematical aggregate functions on collections of values from the database.
...
We can define an AGGREGATE FUNCTION operation, using the symbol ℑ (pronounced script F)7, to specify these types of requests as follows:

<grouping attributes> ℑ <function list> (R)

where <grouping attributes> is a list of attributes of the relation specified in R, and <function list> is a list of (<function> <attribute>) pairs. In each such pair, <function> is one of the allowed functions—such as SUM, AVERAGE, MAXIMUM, MINIMUM,COUNT—and <attribute> is an attribute of the relation specified by R. The resulting relation has the grouping attributes plus one attribute for each element in the function list.

ORDER BY (SORT BY), another source:

Since a relation is a set (or a bag), there is no ordering defined for a relation. That is, two relations are the same if they contain the same tuples, irrespective of ordering. However, a user frequently wants the output of a query to be listed in some particular order. We can define an additional operator τ which sorts a relation if we are willing to allow an operator whose output is not a relation, but an ordered list of tuples.

For example, the expression

τLastName,FirstName(Student)

generates a list of all the Student tuples, ordered by LastName (as the primary sort key) then FirstName (as a secondary sort key). (The secondary sort key is used only if two tuples agree on the primary sort key. A sorting operation can list any number of sort keys, from most significant to least significant.)

Related questions

Using group by on multiple columns

I understand the point of GROUP BY x. But how does GROUP BY x, y work, and what does it mean?

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
Retrieving the last record in each group - MySQL

There is a table messages that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 If I run a query select * …

Read More