Skip to main content

Featured

Basics of Querying

    Understanding the Foundations Standard SQL is based on the relational model, which is a mathematical model for data management and manipulation. The relational model was initially created and proposed by Edgar F. Codd in 1969. Since then, it has been explained and developed by Chris Date, Hugh Darwen, and others. the true source for the model’s name is the mathematical concept relation. A relation in the relational model is what SQL calls a table. The two are not synonymous. SQL attempts to represent with a table: a relation has a heading and a body. The heading is a set of attributes (what SQL attempts to represent with columns), each of a given type. An attribute is identified by name and type name. The body is a set of tuples (what SQL attempts to represent with rows). Each tuple’s heading is the heading of the relation. Each value of each tuple’s attribute is of its respective type.  Some of the most important principals to understand about SQL stem from the relational
Post a Comment
Read more
Labels

How to select first row in each group using GROUP BY ?

 While grouping the rows on a certain criteria using GroupBy, instead of retrieving each and every row in a particular group, the requirement sometimes is to select only single row from each of the group.

In this scenario the probable solution could be use the ranking function under a CTE and then only select those records for which rank is 1. 

For example from purchases table the information required is the highest purchase amount of each customer. As there might be multiple purchases for a customer but only single row needs to be fetched. 

WITH CTE AS (
    SELECT p.customer_id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rank
      FROM PURCHASES p)
 SELECT *
   FROM CTE
 WHERE rank = 1

If CTE is not supported by Database

SELECT a.Customer_id,  
         a.customer, 
         a.total
    FROM PURCHASES a
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = a.customer
                              AND y.max_total = a.total
Labels:

Comments

Post a Comment