# Distinct with maxResults fails under SQLServer

**URL:** <https://discourse.hibernate.org/t/distinct-with-maxresults-fails-under-sqlserver/7052>\
**Category:** Hibernate ORM\
**Created:** [December 21, 2022, 11:31pm UTC](https://discourse.hibernate.org/t/distinct-with-maxresults-fails-under-sqlserver/7052 "2022-12-21T23:31:16Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mconner](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mconner/32/1998_2.png) [@mconner](https://discourse.hibernate.org/u/mconner)\
**Post date:** [December 21, 2022, 11:31pm UTC](https://discourse.hibernate.org/t/distinct-with-maxresults-fails-under-sqlserver/7052/1 "2022-12-21T23:31:16Z")

</div>

I’ve got a query using distinct and maxResults on an entity with jpql:

```auto
Query q = session.createQuery("select distinct idTypeConfig from TMIDTypeConfig idTypeConfig");
 if (maxResults != null) q.setMaxResults(maxResults);
List<TMIDTypeConfig> results = q.list();

```

When this executes, the sql generated is:

```auto
21 Dec 2022 17:22:33,078 DEBUG [SQL [Session#2835de0c] main] select distinct top (?) t1_0.ID_TYPE_PK,t1_0.CFIND_ISSUER,t1_0.CREATED_DATE_TIME,t1_0.DESCRIPTION,t1_0.LAST_UPDATED_DATE_TIME,t1_0.NAME from CFG_ID_TYPE_T t1_0 order by @@version

```

This causes an exception:

```auto
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[mssql-jdbc-8.2.1.jre11.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) ~[mssql-jdbc-8.2.1.jre11.jar:?]

```

because hibernate adds “order by @@version” to the generated SQL when using maxResults();

**Is there a way to prevent this? Or is there some other workaround?**

The entity in here is simple, and this query doesn’t need the distinct, but the actual case where I found this in is more complicated, and the query joins to related entities, so the distinct is necessary.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [December 22, 2022, 1:10pm UTC](https://discourse.hibernate.org/t/distinct-with-maxresults-fails-under-sqlserver/7052/2 "2022-12-22T13:10:43Z")

</div>

Please create a JIRA issue for this. The simple workaround is that you simply specify an explicit ordering. The `order by @@version` is only added because in SQL Server it is illegal to use the `top` clause without order by.

---

<div class="post-metadata">

**Author:** ![mconner](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mconner/32/1998_2.png) [@mconner](https://discourse.hibernate.org/u/mconner)\
**Post date:** [December 22, 2022, 3:05pm UTC](https://discourse.hibernate.org/t/distinct-with-maxresults-fails-under-sqlserver/7052/3 "2022-12-22T15:05:24Z")

</div>

Regarding:

> The `order by @@version` is only added because in SQL Server it is illegal to use the `top` clause without order by.

In SQL Server Management Studio, I just ran the following without error:

```auto
select top 10 * from CFG_ID_TYPE_T; 

```

SQL Server version is reported as Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

I created the issue as [[HHH-15928] Distinct with maxResults fails under SQLServer - Hibernate JIRA (atlassian.net)](https://hibernate.atlassian.net/browse/HHH-15928)

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [December 23, 2022, 12:37pm UTC](https://discourse.hibernate.org/t/distinct-with-maxresults-fails-under-sqlserver/7052/4 "2022-12-23T12:37:34Z")

</div>

Might be that it is in subqueries where the order by is required or on older versions. We’ll investigate it. Thanks for the report.