MS-Office-Forum

Zurück   MS-Office-Forum > Microsoft Access & Datenbanken > SQL > SQL-FAQ
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads
Themen-Optionen Ansicht
Alt 19.07.2006, 18:34   #1
strausto
MOF Guru
MOF Guru
Standard Info - Top & Rangfolgen (SQL Server 2000 und höher)

Beschreibung:

Im Grunde bietet die Top Klausel die Möglichkeit die ersten n order n % Datensätze aus einem Resultset zurückzuliefern.

Code:

Syntax:

[ 
     TOP (expression) [PERCENT]
     [ WITH TIES ]
]
Anhand des folgenden Beispiels wird schnell klar wie die Top Klausel arbeitet:

Code:

use tempdb
go
create table tbl_Top
	(
		id	int identity(1,1)	not null,
		[col1]	int		not null,
		[col2]	varchar(10)	not null
	)
go
insert tbl_Top values (1,'a')
insert tbl_Top values (3,'c')
insert tbl_Top values (2,'b')
insert tbl_Top values (4,'d')
insert tbl_Top values (4,'e')	
insert tbl_Top values (5,'f')
go
select top 2 
	*
from
	tbl_Top
go
drop table tbl_Top
go
Resultset:
Code:

id	col1	col2
1	1	a
2	3	c
Wie zu erwarten war beschränkt die Top Klausel die Abfrage auf die ersten zwei Datensätze.
Wichtig in diesem Zusammenhang zu wissen ist, dass die Auswahl der Zeilen ( in diesem Beispiel 2 ) dem Zufall unterliegt! Insofern man die Frage der Rangfolge beantworten will muss man der Abfrage die Sortierreihenfolge durch ein Order By mitteilen.


TOP mit Order By

Code:

select top 3 
	*
from
	tbl_Top
order by 
	col1 desc,
	col2 asc
Resultset:
Code:

id	col1	col2
6	5	f
4	4	d
5	4	e
Es ist durchaus erlaubt in der Order by Klausel mehrere Spalten einzubeziehen und eine Top Klausel auf eine nicht numerische Spalte anzuwenden. Ebenfalls ist ein Prädikat in Form einer Where Klausel denkbar.

TOP With Ties

Beschränken wir die Betrachtung auf folgende Abfrage:

Code:

select top 4 
	col1
from
	tbl_Top
order by 
	col1 asc
Resultset:
Code:

col1
1
2
3
4
Das Ergebnis beinhaltet genau vier Datensätze. Das ist soweit richtig - versucht man jedoch die Frage nach den ersten vier Werten unter Berücksichtigung der Rangfolge in der Spalte col1 zu beantworten, ist das Ergebnis unvollständig! Genau genommen sollte der Datensatz mit der ID 5 ebenfalls in dem Resultset auftauchen. Um dieser Anforderung Rechnung zu tragen, wurde die Top Klausel um „with ties“ erweitert

Code:

select top 4 with ties 
	col1
from
	tbl_Top
order by 
	col1 asc
Resultset:
Code:

col1
1
2
3
4
4
Der Unterschied zwischen einer Top und einer Top with ties Anweisung, liegt also in der Behandlung redundanter Werte nach denen sortiert werden soll. Am besten lässt sich das mit einer Sportdisziplin vergleichen, in der es bei einem Gleichstand von Punkten doppelte Belegungen von Plätzen geben kann. In dem konkreten Fall sind die Werte 4 redundant und daher gleichwertig. Es resultieren also 2 vierte Plätze innerhalb der vereinbarten Rangfolge.

Bevor man sich also Fragen hinsichtlich der Rangfolge beantwortet, sollte man zunächst Gedanken über mögliche Gleichwertigkeiten anstellen.


TOP Percent

Sind wir an den ersten n Prozent einer Population interessiert, bietet uns der SQL Server die Erweiterung der Top Klausel um „Percent“.

Percent gibt n Percent Datensätze zurück, wobei immer die nächst größere Ganzzahl die tatsächliche Anzahl der Datensätze bestimmt.

Konkret bedeutet das in unserem Beispiel, dass Top 33 2 und Top 34 schon 3 Datensätze listet.

Berechnung:

Percent / 100 * Gesamtdatensätze aus der originären Tabelle = Anzahl Datensätze Resultset

33 / 100 * 6 = 1,98 entspricht 2 Datensätze
34 / 100 * 6 = 2,04 entspricht 3 Datensätze

Code:

select top 60 percent
	col1
from
	tbl_Top
order by 
	col1 asc
Resultset:
Code:

col1
1
2
3
4

TOP Percent With Ties

Die Verwendung von Percent in Verbindung mit With Ties ist ebenfalls möglich:

Code:

select top 60 percent with ties
	col1
from
	tbl_Top
order by 
	col1 asc
Resultset:
Code:

col1
1
2
3
4
4
Eigentlich sollten hier nur vier Datensätze zurückgeben werden, da die Berechnung (siehe oben) 3,6 als Anzahl ergibt. Jedoch wird der letzte Datensatz ergänzt da dieser mit dem vorherigen gleichwertig ist, obwohl damit eigentlich 83,3 % ausgeben werden.


Jeder zweite Datensatz

Will man beispielsweise jeden zweiten Datensatz innerhalb eines Resultsets zurückgeben, bietet sich folgende Lösung an:

Code:

select 
	id,
	col1,
	col2
from
	tbl_Top
where
	(select count(*) from tbl_Top A where A.id <= tbl_Top.ID) % 2 = 1
Resultset:
Code:

id	col1	col2
1	1	a
3	2	b
5	4	e
Diese Lösung funktioniert immer dann, wenn in der Unterabfrage ein eindeutiger Wert (z.B. Primärschlüssel oder Identity Col) verglichen wird. Vergleicht man nicht eindeutige Werte kommt es zu einem falschen Ergebnis:

Code:

select 
	id,
	col1,
	col2
from
	tbl_Top
where
	(select count(*) from tbl_Top A where A.col1 <= tbl_Top.col1) % 2 = 1
Resultset:
Code:

id	col1	col2
1	1	a
2	3	c
4	4	d
5	4	e
Eigentlich sollte man mit Rücksicht auf die Regeln für die Erstellung einer Datenbank immer einen eindeutigen Schlüssel je Tabelle einrichten, jedoch beweist die Praxis anderes. Daher gibt es für diese Situation einen anderen Ansatz.

Setzen wir voraus, dass wir keine eindeutige Spalte (ID) in unsere Tabelle haben.

Code:

use tempdb
go
create table tbl_Top
	(
		[col1]	int		not null,
		[col2]	varchar(10)	not null
	)
--ID eindeutige Spalte (ID) fehlt jetzt.
go
insert tbl_Top values (1,'a')
insert tbl_Top values (3,'c')
insert tbl_Top values (2,'b')
insert tbl_Top values (4,'d')
insert tbl_Top values (4,'e')	
insert tbl_Top values (5,'f')
go
declare @temp_tbl_top	table	(
			id	int identity(1,1)	not null,
			[col1]	int		not null,
			[col2]	varchar(10)	not null
				)


insert @temp_tbl_top
Select 
	col1,
	col2
from
	tbl_Top

Select 
	*
from 
	@temp_tbl_top
go
drop table tbl_Top
go
Damit wird eine temporäre Tabelle erzeugt, die eine Kopie der originären Tabelle darstellt, jedoch erweitert wird um eine eindeutige Spalte. Somit kann die oben aufgeführte Anfrage wieder ausgeführt werden.


Der n - te und m - te Datensatz

Um eine Rückgabe des zweiten und fünften Datensatzes zu erreichen, ist folgende Abfrage einzustellen.

Code:

use tempdb
go
create table tbl_Top
	(
		[id]	int	identity(1,1)	not null,
		[col1]	int			not null,
		[col2]	varchar(10)		not null
	)
go
insert tbl_Top values (1,'a')
insert tbl_Top values (3,'c')
insert tbl_Top values (2,'b')
insert tbl_Top values (4,'d')
insert tbl_Top values (4,'e')	
insert tbl_Top values (5,'f')
go
Select 
	*
from
	tbl_Top
where
	(select count(*) from tbl_Top A where A.ID <= tbl_Top.ID) IN (2,5)
go
drop table tbl_Top
go
Resultset:
Code:

id	col1	col2
2	3	c
5	4	e
Alternative:
Code:

Select 
	A.*
from
	tbl_Top A
join
	tbl_Top B
ON
	B.ID >= A.ID
group by 
	A.ID,
	A.Col1,
	A.Col2
Having 
	count(*) IN (2,5)
Resultset:
Code:

id	col1	col2
2	3	c
5	4	e
Wobei zu erwähnen ist, dass die Abfrage mit dem auf der Grundlage des Inner Joins bei großen Daten deutlich länger dauert als die Erstellung der temporären Tabelle mit der Identity Column.


TOP With Ties je Gruppe

Will man jeweils die ersten zwei Datensätze einer Gruppe auswerten, kann man folgende Lösung anwenden.

Code:

use tempdb
go
create table tbl_Top
	(
		[id]	int	identity(1,1)	not null,
		[col1]	int			not null,
		[col2]	varchar(10)		not null
	)
go
insert tbl_Top values (1,'a')
insert tbl_Top values (2,'b')
insert tbl_Top values (3,'c')
insert tbl_Top values (4,'d')
insert tbl_Top values (4,'e')	
insert tbl_Top values (5,'f')
insert tbl_Top values (0,'a')
insert tbl_Top values (5,'b')
insert tbl_Top values (-1,'c')
insert tbl_Top values (5,'d')
insert tbl_Top values (0,'e')	
insert tbl_Top values (-9,'f')
insert tbl_Top values (5,'a')
insert tbl_Top values (2,'b')
insert tbl_Top values (-3,'c')
insert tbl_Top values (5,'d')
insert tbl_Top values (4,'e')	
insert tbl_Top values (1,'f')
insert tbl_Top values (1,'a')
insert tbl_Top values (-1,'b')
insert tbl_Top values (2,'c')
insert tbl_Top values (5,'d')
insert tbl_Top values (4,'e')	
insert tbl_Top values (5,'f')
go
Select 
	A.id,
	A.col1,
	A.col2,
	col3	=	(
				Select 
					count(*) 
				from 
					tbl_Top B 
				where
						A.col2 = B.col2
					AND 	A.col1 > B.col1
			) + 1
from
	tbl_Top A
where 
	(	Select 
			count(*) 
		from 
			tbl_Top B 
		where
			A.col2 = B.col2
		AND 	A.col1 > B.col1
					
	) < 2 
order by 
	col2,
	col1
go
drop table tbl_Top
go
Resultset:
Code:

id	col1	col2	col3
7	0	a	1
1	1	a	2
19	1	a	2
20	-1	b	1
14	2	b	2
2	2	b	2
15	-3	c	1
9	-1	c	2
4	4	d	1
10	5	d	2
16	5	d	2
22	5	d	2
11	0	e	1
5	4	e	2
23	4	e	2
17	4	e	2
12	-9	f	1
18	1	f	2
Diese Art der Abfrage verhält sich wie eine Top With Ties Anweisung. Gleichwertige Werte in Col1 werden berücksichtigt. Das order by in der Abfrage und die abgeleitete Spalte col3 dienen lediglich dem besseren Verständnis respektive Visualisierung und sind daher optional.

TOP je Gruppe

Will man auf doppelwertige Nennungen innerhalb einer Gruppe verzichten und so eine klassische Top Anweidung nachstellen, ist das mit einem weiteren Prädikat möglich:

Code:

Select 
	A.id,
	A.col1,
	A.col2,
	col3	=	(
				Select 
					count(*) 
				from 
					tbl_Top B 
				where
						A.col2 = B.col2
					AND 	A.col1 > B.col1
			) + 1,
	col4	=	(
				Select 
					count(*) 
				from 
					tbl_Top B 
				where
					A.col2 = B.col2
					AND A.col1 = B.col1
					AND A.ID > B.ID
			) 
from
	tbl_Top A
where 
	(	Select 
			count(*) 
		from 
			tbl_Top B 
		where
			A.col2 = B.col2
		AND A.col1 > B.col1
					
	) < 2 

AND
	(
		Select 
			count(*) 
		from 
			tbl_Top B 
		where
			A.col2 = B.col2
			AND A.col1 = B.col1
			AND A.ID > B.ID
	) = 0
order by 
	col2,
	col1
Resultset:
Code:

id	col1	col2	col3	col4
7	0	a	1	0
1	1	a	2	0
20	-1	b	1	0
2	2	b	2	0
15	-3	c	1	0
9	-1	c	2	0
4	4	d	1	0
10	5	d	2	0
11	0	e	1	0
5	4	e	2	0
12	-9	f	1	0
18	1	f	2	0
Auch hier sind die abgeleiteten Spalten und das order by überflüssig.


Bemerkungen

Die Top Klausel steht erst ab SQL Server 2000 zur Verfügung. Auf dem SQL Server 7 kann man auf SET ROWCOUNT n zurückgreifen. Diese Funktionalität steht weiterhin auf dem SQL Server 2000 und höher aus Gründen der Abwärtskompatibilität zur Verfügung. Man sollte jedoch die Top Klausel präferieren, da der Abfrageoptimierer mit derTop Klausel besser umgehen kann, als mit der SET ROWCOUNT Funktion.
Ein Index auf der zu sortierenden Spalte innerhalb einer Top Anweisung kann die Ausführung deutlich beschleunigen. Verwendet man stetig eine Top Anweisung für eine bestimmte Tabelle empfiehlt es sich, den Index entsprechend zu setzten.

__________________

Best regards

Torsten

Microsoft Certified Technology Specialist Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance
Microsoft Certified Technology Specialist Microsoft® SQL Server® 2008 Database Development
Member of PASS Professional Association for SQL Server http://www.sqlpass.de/

Geändert von strausto (03.05.2007 um 13:51 Uhr).
strausto ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 02:32 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

Copyright ©2000-2018 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günter Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.