DataQuery Order By and Distinct

Mark Elliott asked on September 15, 2015 00:23

Hello,

I am having a rather odd issue with the DataQuery api. I am using both Distinct(true) and OrderBy descending but it doesn't seem to like using them both together. If I take off the OrderBy and just use Distinct the results are distinct but ordered ascending not descending. If I take off the Distinct the results are ordered descending but with duplicates.

It's very puzzling. Any thoughts?

Thanks!

dataquery order by distinct

Recent Answers

Maarten van den Hooven answered on September 15, 2015 09:10

It is more an SQL issue than an Kentico problem, see for example this question on stackoverflow

So you need to specify the columns in the DataQuery api.

0 votesVote for this answer Mark as a Correct answer

Mark Elliott answered on September 15, 2015 16:52

Hi Maarten, thanks for the response. Yes I am including the column I am OrderingBy (DocumentPublishFrom) in my column list. When I take the query and run it in SQL I get exactly the results I expect, a distinct list sorted in decending order. It is only when I run it through the DataQuery API that it does not respect my OrderBy if I also have Distinct (see below)

DataSet newslineItems = new DataQuery(QueryName)
                .Distinct(true)
                .Columns(Columns)
                .Where(WhereCondition)
                .OrderBy(OrderDirection.Descending,OrderBy)
                .Page(QueryHelper.GetInteger("i", 0), QueryHelper.GetInteger("s", 100))
                .Execute();

0 votesVote for this answer Mark as a Correct answer

Joshua Adams answered on September 15, 2015 19:31

You could alway use linq or run a subquery on the result set. I would think that the above would work fine, not sure why it wouldn't.

0 votesVote for this answer Mark as a Correct answer

Maarten van den Hooven answered on September 15, 2015 23:00

I agree with Joshua, I would expect that it works. Hopefully somebody from Kentico can have a look at this. You can always send an mail to support they answer real quickly and offer quality support. If you known what went wrong please let us know here on Devnet. Maybe it is an bug and you earned a tree :-)

0 votesVote for this answer Mark as a Correct answer

Mark Elliott answered on September 17, 2015 18:09

Thanks Maarten, I have ran some more tests and it all seems to come down to having the Distinct() in there (or not). If Distinct() is included it does not respect the OrderBy, if Distinct() is removed it orders properly. I will send an email to support.

1 votesVote for this answer Mark as a Correct answer

Joshua Adams answered on September 17, 2015 20:02

I see that you are using a queryname, is this using a custom query? If so, does the query have the proper macros in to ensure that distinct works appropriately? Just a thought, as I have used this with Kentico queries, and not had any issues, even with it set up the same way.

0 votesVote for this answer Mark as a Correct answer

Mark Elliott answered on September 17, 2015 21:08

Yes it does call a custom query. I have the ##DISTINCT## macro in the query. I am talking with support right now and it seems to be an issue with paging that is causing the sorting to revert to ascending.

1 votesVote for this answer Mark as a Correct answer

Mark Elliott answered on September 22, 2015 18:07

Just wanted to give an update on this issue. It has been confirmed as a bug that will be resolved in the next Hotfix. Thanks everyone for taking a look at this!

1 votesVote for this answer Mark as a Correct answer

Maarten van den Hooven answered on September 26, 2015 21:10

Hi Mark, You are welcome and gratulations with your Kentico Bug Tree :-)

0 votesVote for this answer Mark as a Correct answer