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

Answer 1

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

Answer 2

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