-1

I have this query:

SELECT TOP (1000) [id]
      ,[name]
      ,[dob]
FROM [Production].[dbo].[test]
ORDER by [dob] ASC

Result:

id name dob
1 Danny Small 1/1/2009
3 Victor Brady 1/1/2009
4 Maximus Hoffman 1/1/2009
9 Rosa Braun 1/1/2009
10 Marley Chapman 1/1/2009
11 Cristian Chaney 1/1/2009
6 Shayla Farrell 1/1/2009
7 Tom Summers 1/1/2009
15 Madison Reid 1/1/2009
16 Nelson Green 1/1/2009
18 Dalton Duran 1/1/2009
24 Karina Giles 1/1/2009
25 Jon Jon 1/1/2009
17 Riya Webster 1/3/2009
12 Alexus Cisneros 1/3/2009
13 Ann Mcclure 1/3/2009
14 Aryan Cortez 1/3/2009
5 Stefan Tamburyn 1/3/2009
23 Dalton Duran 1/3/2009
2 Eva Hobbs 1/5/2009
8 Elliott Acosta 1/5/2009
22 Kadin Mcclure 1/5/2009

Now I want to get first 10 records:

SELECT [id]
      ,[name]
      ,[dob]
FROM [Production].[dbo].[test]
ORDER by [dob] ASC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Result:

id name dob
1 Danny Small 1/1/2009
11 Cristian Chaney 1/1/2009
10 Marley Chapman 1/1/2009
9 Rosa Braun 1/1/2009
4 Maximus Hoffman 1/1/2009
3 Victor Brady 1/1/2009
7 Tom Summers 1/1/2009
6 Shayla Farrell 1/1/2009
15 Madison Reid 1/1/2009
16 Nelson Green 1/1/2009

Now I want to get second 10 records:

SELECT [id]
      ,[name]
      ,[dob]
FROM [Production].[dbo].[test]
ORDER by [dob] ASC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Result:

id name dob
7 Tom Summers 1/1/2009
6 Shayla Farrell 1/1/2009
25 Jon Jon 1/1/2009
5 Stefan Tamburyn 1/3/2009
14 Aryan Cortez 1/3/2009
13 Ann Mcclure 1/3/2009
12 Alexus Cisneros 1/3/2009
17 Riya Webster 1/3/2009
23 Dalton Duran 1/3/2009
22 Kadin Mcclure 1/5/2009

As you can see records:
7 Tom Summers 1/1/2009
6 Shayla Farrell 1/1/2009

are duplicated in first 10 records and second 10 records. I understand that ORDER BY is messing around.

Solution could be adding another ORDER BY but what if have to use only one column to ORDER BY and some cells may have same value?

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
DevMaster
  • 1
  • 2
  • 1
    It's working as expected, you have no tiebreaker. As all the rows have the *same* value for `dob` you are getting 10 **arbitrary** rows. – Thom A Apr 13 '23 at 14:04
  • Adding to the comment by @ThomA, the order by columns must be unique for the results to be deterministic. – Dan Guzman Apr 13 '23 at 14:05
  • Thank you, the question is what if I do not have unique values in any column, is it now default to be by default added column like id? – DevMaster Apr 13 '23 at 14:15
  • @DevMaster Then you should fix that, because that's a significant design flaw with the schema and you won't be able to reliably do this without it. And remember: the uniqueness can cross multiple columns. – Joel Coehoorn Apr 13 '23 at 14:18
  • Add an identity field so you will have unique values, why do you want to have unrestricted duplicates though. And most of all, why are you fetching them page-wise. Also, "cells" is an excel concept, databases have columns and rows – siggemannen Apr 13 '23 at 21:08

2 Answers2

1

You must assure the ORDER BY clause gives a unique order to the sequence.

In your case, dob is not a unique key for the table. Add an order condition that makes it unique: [id] after the [dob] field.

eg.

SELECT [id]
      ,[name]
      ,[dob]
FROM [Production].[dbo].[test]
ORDER by [dob] ASC, [id]
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Roberto Ferraris
  • 465
  • 3
  • 8
  • Thank you, the question is what if I do not have unique values in any column, is it now default to be by default added column like id? – DevMaster Apr 13 '23 at 14:15
  • having no key into your tables is a devil anti pattern concept.... One way to get around is to use the NEWID() function to generate a dynamic unique data – SQLpro Apr 13 '23 at 14:26
  • The `ORDER BY` has no default, so you must provide a unique order by. Not necessary you must provide a unique field (or a key column), you could provide any combination of fields that make the sort order predictible. – Roberto Ferraris Apr 13 '23 at 15:00
1

what if [I] have to use only one column to ORDER BY and some cells may have same value?

That's an artificial constraint you'll need to remove.

Relational databases explicitly disclaim any responsibility at all for preserving any inherent or natural order for data. There is no such thing as any default or fallback ordering. A database is free to return data in the order it deems most efficient for the situation, and without explicit ORDER BY instructions they can and will in some circumstances return different orders for the same query and source data from run to run.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794