locked
How to select one row from multiple rows RRS feed

  • Question

  • User2029810015 posted

    We have columns A,B,C,D,E and we want to filter data on the basis of E & G condition is only one E which is latest on the basis of E

    Result should be

    A B C D E F G
    7 150 115139 1712910 1099684 1026877 10/28/2016
    16 150 115139 1712910 1099676 1026877 10/31/2016
    70 150 115139 1712910 1099679 1026877 11/3/2016
    60 150 115139 1712910 1099653 1026877 11/6/2016

    Table Data

    A B C D E F G
    6 150 115132 1695418 1085184 1012542 10/27/2016
    7 150 115139 1712910 1099684 1026877 10/28/2016
    12 150 115139 1712910 1099676 1026877 10/29/2016
    14 150 115139 1712910 1099676 1026877 10/30/2016
    16 150 115139 1712910 1099676 1026877 10/31/2016
    18 150 115139 1712910 1099679 1026877 11/1/2016
    20 150 115139 1712910 1099679 1026877 11/2/2016
    70 150 115139 1712910 1099679 1026877 11/3/2016
    72 150 115139 1712910 1099653 1026877 11/4/2016
    58 150 115139 1712910 1099653 1026877 11/5/2016
    60 150 115139 1712910 1099653 1026877 11/6/2016


    Tuesday, November 1, 2016 5:18 PM

Answers

  • User-967720686 posted

    Hi, 

    You can try the code below. 

    @@Senthilwaits Partition By columns are incorrect in your script. I Just fixed them. 

    Declare @Tab Table (A Int, B Int, C Int, D Int, E Int, F Int, G date)
    Insert Into @Tab 
    Select 6,	150,	115132,	1695418,	1085184,	1012542,	'10/27/2016' Union
    Select 7,	150,	115139,	1712910,	1099684,	1026877,	'10/28/2016' Union
    Select 12,	150,	115139,	1712910,	1099676,	1026877,	'10/29/2016' Union
    Select 14,	150,	115139,	1712910,	1099676,	1026877,	'10/30/2016' Union
    Select 16,	150,	115139,	1712910,	1099676,	1026877,	'10/31/2016' Union
    Select 18,	150,	115139,	1712910,	1099679,	1026877,	'11/1/2016'  Union
    Select 20,	150,	115139,	1712910,	1099679,	1026877,	'11/2/2016'  Union
    Select 70,	150,	115139,	1712910,	1099679,	1026877,	'11/3/2016'  Union
    Select 72,	150,	115139,	1712910,	1099653,	1026877,	'11/4/2016'  Union
    Select 58,	150,    115139,	1712910,	1099653,	1026877,	'11/5/2016'  Union
    Select 60,	150,	115139,	1712910,	1099653,	1026877,	'11/6/2016' 
    
    
    Select	T1.*
    From	@Tab T1 
    		Inner Join (
    			Select	MAX(G) G, E
    			From	@Tab 
    					Group By E 
    		) T2 On T1.G = T2.G And T1.E = T2.E 			
    		Order By T1.G 
    		
    --- OR -----		
    ;		
    WITH numbered AS ( 
      SELECT	A, B, C, D, E, F, G,
    			ROW_NUMBER() OVER (PARTITION BY [E] ORDER BY [G] DESC) AS rownum
      FROM @Tab 
    )
    SELECT A, B, C, D, E, F, G
    FROM numbered
    WHERE rownum = 1
    	  Order By G	

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 1, 2016 11:29 PM

All replies

  • User-654786183 posted

    Try this

    select colA, max(colB) from test group by colA

    Tuesday, November 1, 2016 5:31 PM
  • User2029810015 posted

    Try this

    select colA, max(colB) from test group by colA

    I modified my question. please have a look.

    Tuesday, November 1, 2016 8:45 PM
  • User-654786183 posted

    You can try this

    WITH numbered AS
    ( SELECT A, B, C, D, E, F, G,
         ROW_NUMBER() OVER (PARTITION BY [A] ORDER BY [E] DESC) AS rownum
      FROM test
    )
    SELECT A, B, C, D, E, F, G
    FROM numbered
    WHERE rownum = 1;

    Please don't change the original question.  it will confuse the people who are looking into the thread.  You can always reply to the thread or start a new thread

    Tuesday, November 1, 2016 8:57 PM
  • User-967720686 posted

    Hi, 

    You can try the code below. 

    @@Senthilwaits Partition By columns are incorrect in your script. I Just fixed them. 

    Declare @Tab Table (A Int, B Int, C Int, D Int, E Int, F Int, G date)
    Insert Into @Tab 
    Select 6,	150,	115132,	1695418,	1085184,	1012542,	'10/27/2016' Union
    Select 7,	150,	115139,	1712910,	1099684,	1026877,	'10/28/2016' Union
    Select 12,	150,	115139,	1712910,	1099676,	1026877,	'10/29/2016' Union
    Select 14,	150,	115139,	1712910,	1099676,	1026877,	'10/30/2016' Union
    Select 16,	150,	115139,	1712910,	1099676,	1026877,	'10/31/2016' Union
    Select 18,	150,	115139,	1712910,	1099679,	1026877,	'11/1/2016'  Union
    Select 20,	150,	115139,	1712910,	1099679,	1026877,	'11/2/2016'  Union
    Select 70,	150,	115139,	1712910,	1099679,	1026877,	'11/3/2016'  Union
    Select 72,	150,	115139,	1712910,	1099653,	1026877,	'11/4/2016'  Union
    Select 58,	150,    115139,	1712910,	1099653,	1026877,	'11/5/2016'  Union
    Select 60,	150,	115139,	1712910,	1099653,	1026877,	'11/6/2016' 
    
    
    Select	T1.*
    From	@Tab T1 
    		Inner Join (
    			Select	MAX(G) G, E
    			From	@Tab 
    					Group By E 
    		) T2 On T1.G = T2.G And T1.E = T2.E 			
    		Order By T1.G 
    		
    --- OR -----		
    ;		
    WITH numbered AS ( 
      SELECT	A, B, C, D, E, F, G,
    			ROW_NUMBER() OVER (PARTITION BY [E] ORDER BY [G] DESC) AS rownum
      FROM @Tab 
    )
    SELECT A, B, C, D, E, F, G
    FROM numbered
    WHERE rownum = 1
    	  Order By G	

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 1, 2016 11:29 PM