1 개요

2 테스트 데이터

3 2000 버전의 솔루션

4 2005 버전의 솔루션

다음의 테이블에서 결과와 같이 그룹 중 첫 번째 값만 나타내게 하는 패턴이다. 일반적으로 보고서 작성시 쓰인다.

Seq         GameType             GameName             ConnCount
----------- -------------------- -------------------- -----------
1           MMORPG               뮤                  588456
2           MMORPG               리니지              25476
3           FPS                  헉슬리              2352
4           MMORPG               썬                  34576
5           FPS                  스페셜포스          23452
6           MMORPG               십이지천            45234
7           CASUAL               파르페              34234
8           CASUAL               위키                12121
9           CASUAL               라그라로크          11111
 
 
--결과
GameType             GameName             ConnCount
-------------------- -------------------- -----------
CASUAL               파르페              588456
                     라그라로크          25476
                     위키                2352
FPS                  스페셜포스          34576
                     헉슬리              23452
MMORPG               십이지천            45234
                     리니지              34234
                     뮤                  12121
                     썬                  11111

CREATE TABLE #temp(
	Seq int
,	GameType varchar(20)
,	GameName nvarchar(20)
,	ConnCount int
)
INSERT #temp VALUES(1, 'MMORPG', N'뮤', 12121)
INSERT #temp VALUES(2, 'MMORPG', N'리니지', 34234)
INSERT #temp VALUES(3, 'FPS'   , N'헉슬리', 23452)
INSERT #temp VALUES(4, 'MMORPG', N'썬', 11111)
INSERT #temp VALUES(5, 'FPS'   , N'스페셜포스', 34576)
INSERT #temp VALUES(6, 'MMORPG', N'십이지천', 45234)
INSERT #temp VALUES(7, 'CASUAL', N'파르페', 588456)
INSERT #temp VALUES(8, 'CASUAL', N'위키', 2352)
INSERT #temp VALUES(9, 'CASUAL', N'라그라로크', 25476)

use pubs
go
 
set statistics io on
select
	isnull(b.type, '') type
,	a.title_id
,	a.title
from titles a 
	left outer join (
		select
			type
		,	min(title_id) title_id
		from titles
		group by type
) b
on a.title_id = b.title_id

/*
type         title_id title
------------ -------- --------------------------------------------------------------------------------
business     BU1032   The Busy Executive's Database Guide
             BU1111   Cooking with Computers: Surreptitious Balance Sheets
             BU2075   You Can Combat Computer Stress!
             BU7832   Straight Talk About Computers
mod_cook     MC2222   Silicon Valley Gastronomic Treats
             MC3021   The Gourmet Microwave
UNDECIDED    MC3026   The Psychology of Computer Cooking
popular_comp PC1035   But Is It User Friendly?
             PC8888   Secrets of Silicon Valley
             PC9999   Net Etiquette
psychology   PS1372   Computer Phobic AND Non-Phobic Individuals: Behavior Variations
             PS2091   Is Anger the Enemy?
             PS2106   Life Without Fear
             PS3333   Prolonged Data Deprivation: Four Case Studies
             PS7777   Emotional Security: A New Algorithm
trad_cook    TC3218   Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
             TC4203   Fifty Years in Buckingham Palace Kitchens
             TC7777   Sushi, Anyone?
*/

select 
	isnull(b.GameType, '') GameType
,	a.GameName
,	a.ConnCount
from #temp a 
	left join (
		select
			GameType
		,	min(GameName) GameName
		from #temp
		group by GameType
	) b
on a.GameName = b.GameName
order by a.GameType, a.GameName

/*
GameType             GameName             ConnCount
-------------------- -------------------- -----------
CASUAL               라그라로크              25476
                     위키                    2352
                     파르페                  588456
FPS                  스페셜포스              34576
                     헉슬리                  23452
MMORPG               리니지                  34234
                     뮤                      12121
                     십이지천                45234
                     썬                      11111
*/

--2005 방식, 조낸 간단허다.. 
SELECT
	CASE WHEN RowNum > 1 THEN '' ELSE GameType END GameType
,	GameName
,	ConnCount
FROM (
	SELECT
		GameType
	,	GameName
	,	ConnCount
	,	ROW_NUMBER() OVER(PARTITION BY GameType ORDER BY GameType, ConnCount DESC) RowNum
	FROM #temp
) T
--옛날 홈페이지에 있는거 편집하려니까 졸라 빡시다...