All Forums General
Dimaga 2 posts Joined 11/15
04 Nov 2015
Teradata Querydsl distinct limit query.

Hi guys, 
I'm using java querydsl library to generate query to teradata. I want to build query with distinct values and limit. I'm expecting query like:
    select distinct ts.Gender
    from table ts
    order by ts.Gender asc
    limit 1000
(this query also generated by querydsl for another sql database)
and for teradata it gives me following query
    select distinct ts.Gender
    from table ts
    order by ts.Gender asc
    qualify row_number() over (order by ts.Gender asc) <= 1000;
but this two queris give different results, qualify part works before select part. So first it sorting, than select first 1000 ows and than apply distinct and return only 1 value.
Also I was thinking about using top N but :
1)it does not work with distinct
2)it's hard to apply it in querydsl
So my question is:
1) What is the best way to make second query work as first one
2) Maybe someone can advice how to achive this with querydsl library?

tomnolan 594 posts Joined 01/08
04 Nov 2015

Your problem is due to ordering by Gender in the qualify clause.
You would need to use a different order-by column in the qualify clause; specifically, something like an ID or LastName column that is not correlated with Gender, so that you obtain both Gender values in your sample of 1000 rows.

Dimaga 2 posts Joined 11/15
05 Nov 2015

In in first query it works like this: first it select all distinct values, then it order them, and then returns first thousand of ordered result. And I need second one works in same way.
My query can be applied to different columns in table (Gender is just example), so there is a chanse that there will be more than 1000 of distinct values. And if I will first order, that select 1000 sample and then apply distinct there is a chanse I will get less than 1000 distinct values. 

tomnolan 594 posts Joined 01/08
05 Nov 2015

GROUP BY produces distinct values, and works with TOP n.
select top 1000 ts.Gender from table ts group by ts.Gender order by ts.Gender asc

You must sign in to leave a comment.