none
Where clause help?

    Question

  • Experts,

    I've sample values as:-

    CREATE TABLE #TEMP
    (
    	Key_Out	INT,
    	Src		VARCHAR(10)
    )
    INSERT INTO #TEMP VALUES ( 18326, 'DW' )
    INSERT INTO #TEMP VALUES ( 18326, 'BI' )
    INSERT INTO #TEMP VALUES ( 18326, 'CRM' )
    INSERT INTO #TEMP VALUES ( 53526, 'DW' )
    INSERT INTO #TEMP VALUES ( 53526, 'BI' )
    SELECT	Key_Out, Src,
    		ROW_NUMBER() OVER (PARTITION BY Key_Out ORDER BY Key_Out) AS Order_ID
    FROM	#TEMP
    --ORDER BY	Order_ID DESC
    DROP TABLE #TEMP

    Expected o/p as :-

    SELECT '18326' AS 'Key_Out', '3' AS 'Src'
    UNION ALL
    SELECT '53526' AS 'Key_Out', '2' AS 'Src'

    I want to display only last value of each Key_Out's.

    Thanks in advance

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS

    Saturday, March 02, 2013 11:55 PM

Answers

  • CREATE TABLE #TEMP
    (
    	Key_Out	INT,
    	Src		VARCHAR(10)
    )
    INSERT INTO #TEMP VALUES ( 18326, 'DW' )
    INSERT INTO #TEMP VALUES ( 18326, 'BI' )
    INSERT INTO #TEMP VALUES ( 18326, 'CRM' )
    INSERT INTO #TEMP VALUES ( 53526, 'DW' )
    INSERT INTO #TEMP VALUES ( 53526, 'BI' )
    ;with mycte as
    (SELECT	Key_Out, Src,
    		ROW_NUMBER() OVER (PARTITION BY Key_Out ORDER BY Src) AS Order_ID,
    		ROW_NUMBER() OVER (PARTITION BY Key_Out ORDER BY Src DESC) AS rn
    FROM	#TEMP)
    
    Select Key_Out, Order_ID   FROM mycte 
    WHERE rn=1
    
    DROP TABLE #TEMP

    • Marked as answer by KaumilGandhi Sunday, March 03, 2013 2:19 AM
    Sunday, March 03, 2013 12:02 AM
    Moderator

All replies

  • CREATE TABLE #TEMP
    (
    	Key_Out	INT,
    	Src		VARCHAR(10)
    )
    INSERT INTO #TEMP VALUES ( 18326, 'DW' )
    INSERT INTO #TEMP VALUES ( 18326, 'BI' )
    INSERT INTO #TEMP VALUES ( 18326, 'CRM' )
    INSERT INTO #TEMP VALUES ( 53526, 'DW' )
    INSERT INTO #TEMP VALUES ( 53526, 'BI' )
    ;with mycte as
    (SELECT	Key_Out, Src,
    		ROW_NUMBER() OVER (PARTITION BY Key_Out ORDER BY Src) AS Order_ID,
    		ROW_NUMBER() OVER (PARTITION BY Key_Out ORDER BY Src DESC) AS rn
    FROM	#TEMP)
    
    Select Key_Out, Order_ID   FROM mycte 
    WHERE rn=1
    
    DROP TABLE #TEMP

    • Marked as answer by KaumilGandhi Sunday, March 03, 2013 2:19 AM
    Sunday, March 03, 2013 12:02 AM
    Moderator
  • Please read any book on RDBMS. Tables have no ordering so there is no "last value"; if you want to have an ordering, you need to a subset of columns to create that relatiosnhip. That is called Codd's Information Principle.

    What you posted is

    SELECT key_out, COUNT(*) FROM #Temp GROUP BY key_out;


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, March 04, 2013 12:12 AM