샘플데이타는 [1]등록일 역순으로 나열이 되고있습니다.
동일날짜에 같은 ID끼리는 그룹으로 보이게 정렬을하려고
[2]처럼 정렬을 했더니 동일날짜에 같은 ID끼리 그룹으로 나열은되는데
기존의 등록순서는 무관하게 정렬이되고있는데
[3]형태처럼 나열을 하려고 하면 어떤식으로 쿼리를 만들어야할까요?
조언부탁드립니다.
감사합니다^^
1] select * from TEST ORDER BY REGDATE DESC
ID REGDATE
20 AAA 2013-01-20 14:01:00.000
19 CCC 2013-01-20 13:19:00.000
18 FFF 2013-01-20 12:29:00.000
17 EEE 2013-01-20 11:02:00.000
16 DDD 2013-01-20 10:32:00.000
15 BBB 2013-01-20 09:43:00.000
14 CCC 2013-01-20 08:18:00.000
13 AAA 2013-01-20 07:09:00.000
12 CCC 2013-01-20 06:23:00.000
11 FFF 2013-01-20 05:45:00.000
10 AAA 2013-01-19 14:24:00.000
09 BBB 2013-01-19 13:44:00.000
08 CCC 2013-01-19 12:16:00.000
07 DDD 2013-01-19 11:27:00.000
06 BBB 2013-01-19 10:36:00.000
05 AAA 2013-01-19 09:45:00.000
04 EEE 2013-01-19 08:30:00.000
03 BBB 2013-01-19 07:08:00.000
02 FFF 2013-01-19 06:10:00.000
01 BBB 2013-01-19 05:05:00.000
2] select * from TEST ORDER BY convert(nchar(10),REGDATE,121) DESC, ID DESC
ID REGDATE
11 FFF 2013-01-20 05:45:00.000
18 FFF 2013-01-20 12:29:00.000
17 EEE 2013-01-20 11:02:00.000
16 DDD 2013-01-20 10:32:00.000
14 CCC 2013-01-20 08:18:00.000
12 CCC 2013-01-20 06:23:00.000
19 CCC 2013-01-20 13:19:00.000
15 BBB 2013-01-20 09:43:00.000
13 AAA 2013-01-20 07:09:00.000
20 AAA 2013-01-20 14:01:00.000
02 FFF 2013-01-19 06:10:00.000
04 EEE 2013-01-19 08:30:00.000
07 DDD 2013-01-19 11:27:00.000
08 CCC 2013-01-19 12:16:00.000
09 BBB 2013-01-19 13:44:00.000
03 BBB 2013-01-19 07:08:00.000
01 BBB 2013-01-19 05:05:00.000
06 BBB 2013-01-19 10:36:00.000
05 AAA 2013-01-19 09:45:00.000
10 AAA 2013-01-19 14:24:00.000
[3] 원하는 나열형태
ID REGDATE
20 AAA 2013-01-20 14:01:00.000
13 AAA 2013-01-20 07:09:00.000
19 CCC 2013-01-20 13:19:00.000
14 CCC 2013-01-20 08:18:00.000
12 CCC 2013-01-20 06:23:00.000
18 FFF 2013-01-20 12:29:00.000
11 FFF 2013-01-20 05:45:00.000
17 EEE 2013-01-20 11:02:00.000
16 DDD 2013-01-20 10:32:00.000
15 BBB 2013-01-20 09:43:00.000
10 AAA 2013-01-19 14:24:00.000
05 AAA 2013-01-19 09:45:00.000
09 BBB 2013-01-19 13:44:00.000
06 BBB 2013-01-19 10:36:00.000
03 BBB 2013-01-19 07:08:00.000
01 BBB 2013-01-19 05:05:00.000
08 CCC 2013-01-19 12:16:00.000
07 DDD 2013-01-19 11:27:00.000
04 EEE 2013-01-19 08:30:00.000
02 FFF 2013-01-19 06:10:00.000
Comment 4
-
항해자™
2013.02.19 15:02
-
에버존
2013.02.19 15:09
답변 정말 감사드립니다^^
-
항해자™
2013.02.19 15:13
데이터 양이 많으면,,,
SELECT row_no
, B.code
, B.reg_date
FROM (
SELECT code
, DATEDIFF(day,0,reg_date) AS reg_date
, ROW_NUMBER() OVER(ORDER BY MAX(reg_date) DESC) AS row_no
FROM #TMP
GROUP BY
code
, DATEDIFF(day,0,reg_date)
) AS A
INNER JOIN
#TMP AS B
ON A.code = B.code
AND A.reg_date = DATEDIFF(day,0,B.reg_date)
ORDER BY row_no이 더 효율적 입니다.
-
에버존
2013.02.19 15:16
감사합니다^^ 많이 배워 갑니다.
CREATE TABLE #TMP
(idx INT
,code CHAR(3)
,reg_date SMALLDATETIME
)
INSERT INTO #TMP
VALUES
('20','AAA','2013-01-20 14:01:00.000')
,('19','CCC','2013-01-20 13:19:00.000')
,('18','FFF','2013-01-20 12:29:00.000')
,('17','EEE','2013-01-20 11:02:00.000')
,('16','DDD','2013-01-20 10:32:00.000')
,('15','BBB','2013-01-20 09:43:00.000')
,('14','CCC','2013-01-20 08:18:00.000')
,('13','AAA','2013-01-20 07:09:00.000')
,('12','CCC','2013-01-20 06:23:00.000')
,('11','FFF','2013-01-20 05:45:00.000')
,('10','AAA','2013-01-19 14:24:00.000')
,('09','BBB','2013-01-19 13:44:00.000')
,('08','CCC','2013-01-19 12:16:00.000')
,('07','DDD','2013-01-19 11:27:00.000')
,('06','BBB','2013-01-19 10:36:00.000')
,('05','AAA','2013-01-19 09:45:00.000')
,('04','EEE','2013-01-19 08:30:00.000')
,('03','BBB','2013-01-19 07:08:00.000')
,('02','FFF','2013-01-19 06:10:00.000')
,('01','BBB','2013-01-19 05:05:00.000')
SELECT row_no
, B.code
, B.reg_date
FROM (
SELECT code
, CONVERT(char(8),reg_date,112) AS reg_date
, ROW_NUMBER() OVER(ORDER BY MAX(reg_date) DESC) AS row_no
FROM #TMP
GROUP BY
code
, CONVERT(char(8),reg_date,112)
) AS A
INNER JOIN
#TMP AS B
ON A.code = B.code
AND A.reg_date = CONVERT(char(8),B.reg_date,112)
ORDER BY row_no
날짜 컬럼에 인덱스가 있다면 성능에 문제가 있을 것 같긴 하네요;;ㅎㅎ