MS sql ile her kategoriden son tarihe göre üç kayıt seçen sorgu

29.10.2014 3465 defa okundu

Kategori, şehir vb. benzeri alanlarda oluşan veri tabanından son tarihe göre veya ilk tarihe göre 3, 5, 10, ... gibi istenen sayıda kayıt elde etmek için RANK fonksiyonundan faydalanarak sonuç elde edebiliriz.

Veri kaynağı için geçici tablo oluşturma

SELECT 1 AS ID,'AHMET1' AS ADI,'DENEME1' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-08-19 12:52:03.000' AS TARIH
INTO #GECICI_TABLO
UNION
SELECT 2 AS ID, 'AHMET2' AS ADI,'DENEME2' AS SOYADI,'ANKARA' AS SEHIR,'2014-01-31 12:00:00.000' AS TARIH
UNION
SELECT 3 AS ID, 'AHMET3' AS ADI,'DENEME3' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-08-31 10:51:22.000' AS TARIH
UNION
SELECT 4 AS ID,'AHMET4' AS ADI,'DENEME4' AS SOYADI,'ANKARA' AS SEHIR,'2014-08-21 12:29:45.000' AS TARIH
UNION
SELECT 5 AS ID,'AHMET5' AS ADI,'DENEME5' AS SOYADI,'İZMİR' AS SEHIR,'2014-08-20 09:22:00.000' AS TARIH
UNION
SELECT 6 AS ID,'AHMET6' AS ADI,'DENEME6' AS SOYADI,'ANKARA' AS SEHIR,'2014-08-13 10:38:20.000' AS TARIH
UNION
SELECT 7 AS ID,'AHMET7' AS ADI,'DENEME7' AS SOYADI,'İZMİR' AS SEHIR,'2014-06-25 12:00:00.000' AS TARIH
UNION
SELECT 8 AS ID,'AHMET8' AS ADI,'DENEME8' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-08-22 02:39:00.000' AS TARIH
UNION
SELECT 9 AS ID,'AHMET9' AS ADI,'DENEME9' AS SOYADI,'İSTANBUL' AS SEHIR,'2013-12-22 10:01:20.000' AS TARIH
UNION
SELECT 10 AS ID,'AHMET10' AS ADI,'DENEME10' AS SOYADI,'İZMİR' AS SEHIR,'2014-09-06 11:07:23.000' AS TARIH
UNION
SELECT 11 AS ID,'AHMET11' AS ADI,'DENEME11' AS SOYADI,'ANKARA' AS SEHIR,'2014-01-18 22:52:03.000' AS TARIH
UNION
SELECT 12 AS ID,'AHMET12' AS ADI,'DENEME12' AS SOYADI,'İZMİR' AS SEHIR,'2014-03-29 16:50:04.000' AS TARIH
UNION
SELECT 13 AS ID,'AHMET13' AS ADI,'DENEME13' AS SOYADI,'ANKARA' AS SEHIR,'2014-06-05 10:22:23.000' AS TARIH
UNION
SELECT 14 AS ID,'AHMET14' AS ADI,'DENEME14' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-06-15 20:25:00.000' AS TARIH
UNION
SELECT 15 AS ID,'AHMET15' AS ADI,'DENEME15' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-02-05 02:45:55.000' AS TARIH
UNION
SELECT 16 AS ID,'AHMET15' AS ADI,'DENEME16' AS SOYADI,'İZMİR' AS SEHIR,'2014-05-03 11:34:16.000' AS TARIH

Oluşturduğumuz geçici tablomuzun içeriği aşağıdaki gibidir.

SELECT * FROM #GECICI_TABLO

ID	ADI	SOYADI	SEHIR	TARIH
1	AHMET1	DENEME1	İSTANBUL	2014-08-19 12:52:03.000
2	AHMET2	DENEME2	ANKARA	2014-01-31 12:00:00.000
3	AHMET3	DENEME3	İSTANBUL	2014-08-31 10:51:22.000
4	AHMET4	DENEME4	ANKARA	2014-08-21 12:29:45.000
5	AHMET5	DENEME5	İZMİR	2014-08-20 09:22:00.000
6	AHMET6	DENEME6	ANKARA	2014-08-13 10:38:20.000
7	AHMET7	DENEME7	İZMİR	2014-06-25 12:00:00.000
8	AHMET8	DENEME8	İSTANBUL	2014-08-22 02:39:00.000
9	AHMET9	DENEME9	İSTANBUL	2013-12-22 10:01:20.000
10	AHMET10	DENEME10	İZMİR	2014-09-06 11:07:23.000
11	AHMET11	DENEME11	ANKARA	2014-01-18 22:52:03.000
12	AHMET12	DENEME12	İZMİR	2014-03-29 16:50:04.000
13	AHMET13	DENEME13	ANKARA	2014-06-05 10:22:23.000
14	AHMET14	DENEME14	İSTANBUL	2014-06-15 20:25:00.000
15	AHMET15	DENEME15	İSTANBUL	2014-02-05 02:45:55.000
16	AHMET15	DENEME16	İZMİR	2014-05-03 11:34:16.000

MS sql ile her kategoriden son tarihe göre üç kayıt seçen sorgu

SELECT SIRA,ADI,SOYADI,SEHIR,TARIH FROM
(SELECT ADI,SOYADI,SEHIR,TARIH,RANK() OVER(PARTITION BY SEHIR ORDER BY TARIH DESC) AS SIRA
FROM #GECICI_TABLO) AS RS
WHERE SIRA<=3

Sonuç

SIRA	ADI	SOYADI	SEHIR	TARIH
1	AHMET4	DENEME4	ANKARA	2014-08-21 12:29:45.000
2	AHMET6	DENEME6	ANKARA	2014-08-13 10:38:20.000
3	AHMET13	DENEME13	ANKARA	2014-06-05 10:22:23.000
1	AHMET3	DENEME3	İSTANBUL	2014-08-31 10:51:22.000
2	AHMET8	DENEME8	İSTANBUL	2014-08-22 02:39:00.000
3	AHMET1	DENEME1	İSTANBUL	2014-08-19 12:52:03.000
1	AHMET10	DENEME10	İZMİR	2014-09-06 11:07:23.000
2	AHMET5	DENEME5	İZMİR	2014-08-20 09:22:00.000
3	AHMET7	DENEME7	İZMİR	2014-06-25 12:00:00.000