TechSapphire Wednesday, 2018-05-23, 4:04 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • Main » 2012 » October » 26 » SQL Complex quries Interview question
    4:13 PM
    SQL Complex quries Interview question
    Read complete article



    ----Basic but important functions
    --ROW_NUMBER()
    --RANK()
    --DENSE_RANK()
    --NTILE()

    --ROW_NUMBER() --Returns a sequential row number with in partition of result set
    use Northwind;
    SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products;

    ----Getting alternate rows
    ------Odd Row
    select * from (SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products) p where p.[Row No.]%2=1;
    ------Even Row
    select * from (SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products) p where p.[Row No.]%2=0;
    ------Row Between
    select * from (SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products) p where p.[Row No.]
    Between 5 and 10;

    ------ With CTE (commmon type expression)
    with result_with_row_no([Row No.],ProductName)
    as
    (SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products)
    select * from result_with_row_no where [Row No.]%2=1;

    ------ RANK() --Returns the rank of each row within the partition of a result set
    select ProductID,COUNT(OrderID) [Number sold], RANK() OVER(order by count(orderid)) [Ranking] from [Order Details]
    group by ProductID order by ranking,[Number sold];

    ------ DENSE_RANK() ----Returns the rank of each row within the partition of a result set without gap;
    select ProductID,COUNT(OrderID) [Number sold], DENSE_RANK() OVER(order by count(orderid)) [Ranking] from [Order Details]
    group by ProductID order by ranking,[Number sold];

    ----- Complex Example -- getting product total quantity sale with group category and rank by sale
    with sale_detail(CatID,ProdID,Prod_Name,Sale)
    as
    (
    select CategoryID,p.ProductID, p.ProductName, COUNT(o.productid) as [Sale] from Products p,[Order Details] o
    where p.ProductID=o.ProductID group by CategoryID,p.ProductID,p.ProductName
    )
    select catid,prodid,prod_name,sale,RANK() over (partition by catid order by sale desc) [Ranking]
     from sale_detail order by catid,ranking;

    ----- NTILE --Distributes the rows in an ordered partition into a specified number of groups.

    Read More ..
    Category: Database | Views: 16314 | Added by: Admin | Tags: Sql complex queries, queries, Complex, sql, SQL Server, Question, Interview | Rating: 2.0/1
    Total comments: 1
    1  
    comment ce système fonctionne

    <a href="http://www.lancelsoldespascherz.com/#13295">lancel ceinture</a> - <a href="http://www.lancelsoldespascherz.com/#21562">Sac Lancel Pas Cher</a> - http://www.lancelsoldespascherz.com/#32567

    Only registered users can add comments.
    [ Registration | Login ]
    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2018