|
select input, stat.app_id, stat.stair, stat.stay, stat.status from
(select input, app_id, stair, stay, status from tb_stat_real_stay
where convert(char(6), input, 12)
= convert(char(6), getdate(), 12)) as stat order by stair, input, app_id asc;
위와 같은 쿼리를 날려
아래와 같은 결과를 얻었습니다.
문제는 'app_id' 당 레코드가 하나만 나와야 되며,
app_id 당 나와야 할 한 개의 레코드는 'input' 컬럼 값 중 가장 마지막에
발생된 정보 즉, 가장 최근 정보 이면 됩니다.
아래의 표기에 '요거' 로 된 부분만 나왔으면 합니다.
위의 쿼리에 어떻게 해야 하는지 궁금 합니다.
input app_id stair stay status
------------------------------------------------------------------
2009-06-15 11:29:01.907 6 1 1 0
2009-06-15 11:29:20.297 3 1 0 0
2009-06-15 11:30:04.900 2 1 0 0
2009-06-15 15:24:32.453 1 1 0 0
2009-06-15 15:24:43.860 5 1 0 0
2009-06-15 15:24:52.640 6 1 0 0
2009-06-15 15:27:10.470 3 1 0 0
2009-06-15 15:27:53.720 2 1 0 0
2009-06-15 15:28:35.530 4 1 0 0
2009-06-15 15:42:57.030 4 1 0 0
2009-06-15 15:42:59.767 1 1 0 0
2009-06-15 15:43:10.800 5 1 0 0
2009-06-15 15:43:20.123 6 1 0 0
2009-06-15 15:43:34.920 3 1 0 0
2009-06-15 15:44:17.657 2 1 0 0
2009-06-15 16:04:05.280 3 1 0 0 ---> 요거
2009-06-15 16:04:47.733 2 1 0 0 ---> 요거
2009-06-15 16:05:30.703 4 1 0 0 ---> 요거
2009-06-15 16:05:33.170 1 1 0 0 ---> 요거
2009-06-15 16:05:43.797 5 1 0 0 ---> 요거
2009-06-15 16:05:54.390 6 1 0 0 ---> 요거
2009-06-15 11:29:37.630 9 2 0 0
2009-06-15 15:23:20.407 9 2 0 0
2009-06-15 15:24:40.280 10 2 0 0
2009-06-15 15:28:21.983 7 2 0 0
2009-06-15 15:28:33.470 8 2 0 0
2009-06-15 15:42:45.500 7 2 0 0
2009-06-15 15:42:54.657 8 2 0 0
2009-06-15 15:43:07.360 10 2 0 0
2009-06-15 15:43:50.687 9 2 0 0
2009-06-15 16:04:20.157 9 2 0 0 ---> 요거
2009-06-15 16:05:15.707 7 2 0 0 ---> 요거
2009-06-15 16:05:28.063 8 2 0 0 ---> 요거
2009-06-15 16:05:40.453 10 2 0 0 ---> 요거
|
