none
select top 5 RRS feed

  • Question

  • i  wanna select top 5 record of my database on the basis of marks, but if a case comes when 6th record or 7th record of my databse has same marks as 5th record marks, 

    then it should include 6 record or even 7th record, but keep in mind if 6th record marks are not same as 5th record, then it normaly select top 5, what should i do? 

    pplzz help
    Tuesday, December 8, 2015 5:36 AM

Answers

All replies

  • i  wanna select top 5 record of my database on the basis of marks, but if a case comes when 6th record or 7th record of my databse has same marks as 5th record marks, 

    then it should include 6 record or even 7th record, but keep in mind if 6th record marks are not same as 5th record, then it normaly select top 5, what should i do? 

    pplzz help
    Tuesday, December 8, 2015 5:38 AM
  • see the section Top with Ties :

    https://msdn.microsoft.com/en-us/library/ms189463.aspx?f=255&MSPPError=-2147217396


    Thanks and regards, Rishabh K

    Tuesday, December 8, 2015 5:42 AM
  • two ways

    1. WITH TIES

    SELECT TOP 5 WITH TIES *
    FROM Table
    ORDER BY Marks DESC

    2. using DENSE_RANK

    SELECT *
    FROM
    (
    SELECT *,
    DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rnk
    FROM Table
    )t
    WHERE Rnk <= 5


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, December 8, 2015 5:46 AM
  • duplicate of

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4e3556ae-67b2-4aba-bb8b-3d263b6014d5/select-top-5-in-sql?forum=transactsql

    please dont open multiple threads for same issue


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, December 8, 2015 5:47 AM
  • You can write it like below

    create table #tblmarks (ID int,marks int)
    insert into #tblmarks values(1,20),(2,30),(3,26),(4,12),(5,57),(6,51),(7,57)
    
    Select  * from (
    	Select Top 5 * from #tblmarks
    	union 
    	select a.ID,a.marks from #tblmarks a join #tblmarks b on a.ID<>b.ID and a.marks=b.marks
    )Q
    
    Drop table #tblmarks
    



    Cheers,

    Amit Tomar

    ---------------------------------------------------

    Please mark this as answer if it solved your query

    Please vote this as helpful if it solved your query

    ---------------------------------------------------

    My Blog My Wiki Page


    • Edited by Amit-Tomar Tuesday, December 8, 2015 5:53 AM
    Tuesday, December 8, 2015 5:47 AM
  • do like below

    create table #tblmarks (ID int,marks int)
    insert into #tblmarks values(1,20),(2,30),(3,26),(4,12),(5,57),(6,52),(7,57)
    
    Select  * from (
    	Select Top 5 * from #tblmarks
    	union 
    	select a.ID,a.marks from #tblmarks a join #tblmarks b on a.ID<>b.ID and a.marks=b.marks
    )Q
    
    Drop table #tblmarks


    Cheers,

    Amit Tomar

    ---------------------------------------------------

    Please mark this as answer if it solved your query

    Please vote this as helpful if it solved your query

    ---------------------------------------------------

    My Blog My Wiki Page

    Tuesday, December 8, 2015 5:59 AM
  • two ways

    1. WITH TIES

    SELECT TOP 5 WITH TIES *
    FROM Table
    ORDER BY Marks DESC

    2. using DENSE_RANK

    SELECT *
    FROM
    (
    SELECT *,
    DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rnk
    FROM Table
    )t
    WHERE Rnk <= 5

    Those two queries do not do the same thing.  The first query returns the highest 5 rows and any ties with the value in the fifth row.  The second returns all the rows with Marks equal to one of the highest 5 distinct values.  To see the difference, run

    Declare @Test Table(Id char(1), Marks int);
    Insert @Test(Id, Marks) Values
    ('A', 5),
    ('B', 1),
    ('C', 3),
    ('D', 2),
    ('E', 4),
    ('F', 5),
    ('G', 4),
    ('I', 7),
    ('J', 2),
    ('K', 5);
    
    
    SELECT TOP 5 WITH TIES *
    FROM @Test
    ORDER BY Marks DESC
    
    SELECT *
    FROM
    (
    SELECT *,
    DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rnk
    FROM @Test
    )t
    WHERE Rnk <= 5
    

    Tom

    Tuesday, December 8, 2015 3:06 PM