Click here to Skip to main content
15,501,968 members
Home / Discussions / Database
   

Database

 
AnswerRe: Extract Data in Single Record Pin
Richard Deeming16-Jul-14 3:42
mveRichard Deeming16-Jul-14 3:42 
AnswerRe: Extract Data in Single Record Pin
PIEBALDconsult17-Jul-14 14:54
professionalPIEBALDconsult17-Jul-14 14:54 
Questionlinked list using CTE Pin
Ali Al Omairi(Abu AlHassan)14-Jul-14 23:29
professionalAli Al Omairi(Abu AlHassan)14-Jul-14 23:29 
AnswerRe: linked list using CTE Pin
Ali Al Omairi(Abu AlHassan)15-Jul-14 0:14
professionalAli Al Omairi(Abu AlHassan)15-Jul-14 0:14 
QuestionOVER (PARTITION BY ORDER BY ) Pin
Ambertje14-Jul-14 5:44
MemberAmbertje14-Jul-14 5:44 
AnswerRe: OVER (PARTITION BY ORDER BY ) Pin
PIEBALDconsult14-Jul-14 6:13
professionalPIEBALDconsult14-Jul-14 6:13 
GeneralRe: OVER (PARTITION BY ORDER BY ) Pin
Ambertje14-Jul-14 6:15
MemberAmbertje14-Jul-14 6:15 
AnswerRe: OVER (PARTITION BY ORDER BY ) Pin
Eddy Vluggen14-Jul-14 6:27
professionalEddy Vluggen14-Jul-14 6:27 
It determines the grouping. Play around with below script;
SQL
BEGIN TRANSACTION

	CREATE TABLE SomeTest(
		Field1 INTEGER,
		Field2 CHAR(1),
		Data VARCHAR(50))
		
	INSERT INTO SomeTest VALUES (1, 'C', 'Test')
	INSERT INTO SomeTest VALUES (1, 'A', 'Test')
	INSERT INTO SomeTest VALUES (7, 'C', 'Test')
	INSERT INTO SomeTest VALUES (7, 'A', 'Test')
	INSERT INTO SomeTest VALUES (4, 'C', 'Test')

    SELECT *
         , ROW_NUMBER() OVER ( PARTITION BY Field2 ORDER BY Field1 ) 
      FROM SomeTest
    
ROLLBACK 

If you change the field that's being partitioned by to "Field1", you'll see a different grouping in the result-set (with each group receiving a unique numbering).

It's often used with a primary key because some idiot forgot to add an identity-field Smile | :)
Bastard Programmer from Hell Suspicious | :suss:
If you can't read my code, try converting it here[^]

GeneralRe: OVER (PARTITION BY ORDER BY ) Pin
PIEBALDconsult14-Jul-14 6:29
professionalPIEBALDconsult14-Jul-14 6:29 
QuestionRe: OVER (PARTITION BY ORDER BY ) Pin
Eddy Vluggen14-Jul-14 8:47
professionalEddy Vluggen14-Jul-14 8:47 
AnswerRe: OVER (PARTITION BY ORDER BY ) Pin
Jörgen Andersson14-Jul-14 9:33
professionalJörgen Andersson14-Jul-14 9:33 
GeneralRe: OVER (PARTITION BY ORDER BY ) Pin
Mycroft Holmes14-Jul-14 13:54
professionalMycroft Holmes14-Jul-14 13:54 
GeneralRe: OVER (PARTITION BY ORDER BY ) Pin
Jörgen Andersson14-Jul-14 23:20
professionalJörgen Andersson14-Jul-14 23:20 
GeneralRe: OVER (PARTITION BY ORDER BY ) Pin
Ambertje14-Jul-14 23:12
MemberAmbertje14-Jul-14 23:12 
AnswerRe: OVER (PARTITION BY ORDER BY ) Pin
jschell14-Jul-14 11:07
Memberjschell14-Jul-14 11:07 
AnswerRe: OVER (PARTITION BY ORDER BY ) Pin
GuyThiebaut14-Jul-14 22:33
professionalGuyThiebaut14-Jul-14 22:33 
GeneralRe: OVER (PARTITION BY ORDER BY ) Pin
Ambertje14-Jul-14 23:13
MemberAmbertje14-Jul-14 23:13 
QuestionError: Can't delete row or update row in SQL Server ? Pin
taibc11-Jul-14 19:29
Membertaibc11-Jul-14 19:29 
AnswerRe: Error: Can't delete row or update row in SQL Server ? Pin
Mycroft Holmes12-Jul-14 0:32
professionalMycroft Holmes12-Jul-14 0:32 
GeneralRe: Error: Can't delete row or update row in SQL Server ? Pin
taibc13-Jul-14 21:36
Membertaibc13-Jul-14 21:36 
GeneralRe: Error: Can't delete row or update row in SQL Server ? Pin
Mycroft Holmes13-Jul-14 22:06
professionalMycroft Holmes13-Jul-14 22:06 
AnswerRe: Error: Can't delete row or update row in SQL Server ? Pin
ZurdoDev14-Jul-14 11:14
professionalZurdoDev14-Jul-14 11:14 
QuestionJOIN vs. WHERE Pin
Klaus-Werner Konrad11-Jul-14 10:22
MemberKlaus-Werner Konrad11-Jul-14 10:22 
AnswerRe: JOIN vs. WHERE Pin
Mycroft Holmes11-Jul-14 15:24
professionalMycroft Holmes11-Jul-14 15:24 
GeneralRe: JOIN vs. WHERE Pin
Klaus-Werner Konrad12-Jul-14 10:08
MemberKlaus-Werner Konrad12-Jul-14 10:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.