MS sql ile her kategoriden son tarihe göre üç kayıt seçen sorgu
4.477 defa okundu
Konu: Veri Analizi
İçindekiler
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
SQL
- SELECT 1 AS ID,'AHMET1' AS ADI,'DENEME1' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-08-19 12:52:03.000' AS TARIHINTO #GECICI_TABLO UNIONSELECT 2 AS ID, 'AHMET2' AS ADI,'DENEME2' AS SOYADI,'ANKARA' AS SEHIR,'2014-01-31 12:00:00.000' AS TARIHUNIONSELECT 3 AS ID, 'AHMET3' AS ADI,'DENEME3' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-08-31 10:51:22.000' AS TARIH UNIONSELECT 4 AS ID,'AHMET4' AS ADI,'DENEME4' AS SOYADI,'ANKARA' AS SEHIR,'2014-08-21 12:29:45.000' AS TARIH UNIONSELECT 5 AS ID,'AHMET5' AS ADI,'DENEME5' AS SOYADI,'İZMİR' AS SEHIR,'2014-08-20 09:22:00.000' AS TARIH UNIONSELECT 6 AS ID,'AHMET6' AS ADI,'DENEME6' AS SOYADI,'ANKARA' AS SEHIR,'2014-08-13 10:38:20.000' AS TARIH UNIONSELECT 7 AS ID,'AHMET7' AS ADI,'DENEME7' AS SOYADI,'İZMİR' AS SEHIR,'2014-06-25 12:00:00.000' AS TARIH UNIONSELECT 8 AS ID,'AHMET8' AS ADI,'DENEME8' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-08-22 02:39:00.000' AS TARIH UNIONSELECT 9 AS ID,'AHMET9' AS ADI,'DENEME9' AS SOYADI,'İSTANBUL' AS SEHIR,'2013-12-22 10:01:20.000' AS TARIH UNIONSELECT 10 AS ID,'AHMET10' AS ADI,'DENEME10' AS SOYADI,'İZMİR' AS SEHIR,'2014-09-06 11:07:23.000' AS TARIH UNIONSELECT 11 AS ID,'AHMET11' AS ADI,'DENEME11' AS SOYADI,'ANKARA' AS SEHIR,'2014-01-18 22:52:03.000' AS TARIH UNIONSELECT 12 AS ID,'AHMET12' AS ADI,'DENEME12' AS SOYADI,'İZMİR' AS SEHIR,'2014-03-29 16:50:04.000' AS TARIH UNIONSELECT 13 AS ID,'AHMET13' AS ADI,'DENEME13' AS SOYADI,'ANKARA' AS SEHIR,'2014-06-05 10:22:23.000' AS TARIH UNIONSELECT 14 AS ID,'AHMET14' AS ADI,'DENEME14' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-06-15 20:25:00.000' AS TARIH UNIONSELECT 15 AS ID,'AHMET15' AS ADI,'DENEME15' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-02-05 02:45:55.000' AS TARIH UNIONSELECT 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.
SQL
- 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
SQL
- 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 |
Teşekkürler, geri bildiriminiz alındı.