locked
Select Top 1 from Distinct RRS feed

  • Question

  • How to select Top 1 from Distinct.
    Table2                                                                 vTable 1 (View)
    ID     Type     year      IDTYPE                               ID      Type     Year 
    ---    ------    -------     --------                               ----     ------     ------  
    123    AP                       1                                    123       AP      2009
    134    UP                       0                                    123       AP      2008
                                                                              123       AP      2007
    what am doing is 
    update table2
    set Year =
    case when IDType = 1
    Then
    (Select Distinct column from vtable1 order by column desc) else '' end .

    In these I need to select the Top 1 and populate to  column Year in table 2

    The out put am looking for is:

    Table2
     ID                 Type        Year
    ------              ------       -------
    123                 AP            2009  
     
    • Changed type Plamen Ratchev Thursday, March 4, 2010 4:02 AM This is a question
    Thursday, March 4, 2010 3:30 AM

Answers

  • Here is one way(SQL 2005 and up)

    DECLARE @Table2 TABLE(ID INT,Type CHAR(2),[Year] INT,IDTYPE INT)
    INSERT INTO @Table2
    SELECT 123,'AP',0,1  UNION ALL
    SELECT 134,'UP',0,0 
    
    DECLARE @vTable TABLE(ID INT,Type CHAR(20),[Year] INT)
    INSERT INTO @vTable
    SELECT 123,'AP',2009 UNION ALL
    SELECT 123,'AP',2008 UNION ALL
    SELECT 123,'AP',2007
    
    SELECT * FROM @Table2
    SELECT * FROM @vTable
    
    ;WITH CTE AS
    (
    SELECT ID,Type,[Year],ROW_NUMBER() OVER(PARTITION BY ID,Type ORDER BY [Year] DESC) AS Row
    FROM @vTable
    )
    UPDATE t2
    SET [Year] = C.[Year]
    FROM @Table2 t2
    	JOIN CTE C ON t2.ID = C.ID
    WHERE Row = 1
    
    SELECT * FROM @Table2
    SELECT * FROM @vTable

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Edited by Abdshall Thursday, March 4, 2010 4:07 AM
    • Marked as answer by adurs Thursday, March 4, 2010 4:08 AM
    Thursday, March 4, 2010 3:44 AM

All replies

  • Here is one way(SQL 2005 and up)

    DECLARE @Table2 TABLE(ID INT,Type CHAR(2),[Year] INT,IDTYPE INT)
    INSERT INTO @Table2
    SELECT 123,'AP',0,1  UNION ALL
    SELECT 134,'UP',0,0 
    
    DECLARE @vTable TABLE(ID INT,Type CHAR(20),[Year] INT)
    INSERT INTO @vTable
    SELECT 123,'AP',2009 UNION ALL
    SELECT 123,'AP',2008 UNION ALL
    SELECT 123,'AP',2007
    
    SELECT * FROM @Table2
    SELECT * FROM @vTable
    
    ;WITH CTE AS
    (
    SELECT ID,Type,[Year],ROW_NUMBER() OVER(PARTITION BY ID,Type ORDER BY [Year] DESC) AS Row
    FROM @vTable
    )
    UPDATE t2
    SET [Year] = C.[Year]
    FROM @Table2 t2
    	JOIN CTE C ON t2.ID = C.ID
    WHERE Row = 1
    
    SELECT * FROM @Table2
    SELECT * FROM @vTable

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Edited by Abdshall Thursday, March 4, 2010 4:07 AM
    • Marked as answer by adurs Thursday, March 4, 2010 4:08 AM
    Thursday, March 4, 2010 3:44 AM
  • Here is one solution (not sure if you need the predicate "V.id = table2.id", you can remove if not needed):

    UPDATE table2
    SET [year] = (SELECT TOP 1 V.[year]
                  FROM vtable1 AS V
                  WHERE V.id = table2.id
                  ORDER BY V.[year] DESC)
    WHERE IDType = 1;

    Plamen Ratchev
    Thursday, March 4, 2010 4:00 AM
  • thanks dude
    • Marked as answer by adurs Thursday, March 4, 2010 4:03 AM
    • Unmarked as answer by Arnie RowlandMVP Thursday, March 4, 2010 4:48 PM
    Thursday, March 4, 2010 4:03 AM
  • Dude I want the top 1 from Distinct
    Thursday, March 4, 2010 4:09 AM
  • Add the DISTINCT key word to the query.

    In many English speaking cultures, the address 'dude' is often not viewed in a positive manner.
    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    Thursday, March 4, 2010 4:49 PM
  • There is no need for DISTINCT. TOP 1 will return only one row, even if there are ties.
    Plamen Ratchev
    Thursday, March 4, 2010 5:50 PM