none
TOP 5TH VALUE FROM THIS TABLE RRS feed

  • Question

  • THIS IS  MY TBALE.TABLE1(C1,C2,C3) . I NEED TOP 5TH C3 VALUE.

     

    SELECT TOP1 C3 FROM (SEELCT DISTINCT TOP 5 C3 FROM TABLE1 ORDER BY C3 DESC) A ORDER BY C3

    IS IT CORRECT? IF IT CORRECT CAN YOU TELL ME THE EXECUTION PROCESS

    THANKS

    CMK..

     

    Thursday, May 6, 2010 11:48 PM

Answers

  • Hi CMK

    Please use this

    --First Create temprary table Table1 and insert value in to column value

    Declare  @table1 table(c1 nvarchar(10),c2 nvarchar(10),c3 nvarchar(10))

     

    INSERT INTO @table1(c1, c2, c3)

    SELECT 'a1','b1','c1' UNION ALL

    SELECT 'a2','b2','c2' UNION ALL

    SELECT 'a3','b3','c3' UNION ALL

    SELECT 'a4','b4','c4' UNION ALL

    SELECT 'a5','b5','c5' UNION ALL

    SELECT 'a6','b6','c6'

     

    select * from @table1

     

    --then give rownumber to each row by row_number() function

     

    SELECT c1,c2,c3,ROW_NUMBER() OVER (ORDER BY c3) AS ROW FROM @TABLE1

     

    --Then

    --Using Common table expression we have find c3 column 5th record by this query

    ;with rownumber as

    (

          SELECT c3,ROW_NUMBER() OVER (ORDER BY c3) AS ROW FROM @TABLE1

    )

    select c3 from rownumber where row = '5'

     

    --and if you want to show execution plan please select whole query and  press CTRL + L

     

    • Proposed as answer by Gopi V Friday, May 7, 2010 9:41 AM
    • Marked as answer by KJian_ Wednesday, May 12, 2010 8:01 AM
    Friday, May 7, 2010 9:40 AM
  • Yes, this is close if you correct the typo.

    select top 1 c3 from (select distinct top 5 c3 from Table1 order by C3) A order by C3 DESC

    In SQL Server 2005+ there is a simpler solution 

    select distinct c3 from (select c3, dense_rank() over (order by c3) as Row from Table1) X where Row = 5


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Gerry Phillips Friday, May 7, 2010 8:44 AM
    • Marked as answer by KJian_ Wednesday, May 12, 2010 8:01 AM
    Friday, May 7, 2010 12:06 AM
    Moderator

All replies

  • Yes, this is close if you correct the typo.

    select top 1 c3 from (select distinct top 5 c3 from Table1 order by C3) A order by C3 DESC

    In SQL Server 2005+ there is a simpler solution 

    select distinct c3 from (select c3, dense_rank() over (order by c3) as Row from Table1) X where Row = 5


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Gerry Phillips Friday, May 7, 2010 8:44 AM
    • Marked as answer by KJian_ Wednesday, May 12, 2010 8:01 AM
    Friday, May 7, 2010 12:06 AM
    Moderator
  • THANK  YOU NAOM....

    Friday, May 7, 2010 12:22 AM
  • Hi CMK

    Please use this

    --First Create temprary table Table1 and insert value in to column value

    Declare  @table1 table(c1 nvarchar(10),c2 nvarchar(10),c3 nvarchar(10))

     

    INSERT INTO @table1(c1, c2, c3)

    SELECT 'a1','b1','c1' UNION ALL

    SELECT 'a2','b2','c2' UNION ALL

    SELECT 'a3','b3','c3' UNION ALL

    SELECT 'a4','b4','c4' UNION ALL

    SELECT 'a5','b5','c5' UNION ALL

    SELECT 'a6','b6','c6'

     

    select * from @table1

     

    --then give rownumber to each row by row_number() function

     

    SELECT c1,c2,c3,ROW_NUMBER() OVER (ORDER BY c3) AS ROW FROM @TABLE1

     

    --Then

    --Using Common table expression we have find c3 column 5th record by this query

    ;with rownumber as

    (

          SELECT c3,ROW_NUMBER() OVER (ORDER BY c3) AS ROW FROM @TABLE1

    )

    select c3 from rownumber where row = '5'

     

    --and if you want to show execution plan please select whole query and  press CTRL + L

     

    • Proposed as answer by Gopi V Friday, May 7, 2010 9:41 AM
    • Marked as answer by KJian_ Wednesday, May 12, 2010 8:01 AM
    Friday, May 7, 2010 9:40 AM
  • If in case if you would like to load the value into the variable , it can be done straight away like this

    DECLARE @X VARCHAR(5000)

     

    SELECT DISTINCT TOP 5 @X = Title  FROM employee ORDER BY Title DESC

     

    SELECT @X     

     

    Friday, May 7, 2010 12:47 PM