Teradata Querydsl distinct limit query.

General
Fan

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?

Tags (1)
3 REPLIES
Teradata Employee

Re: Teradata Querydsl distinct limit query.

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.

Fan

Re: Teradata Querydsl distinct limit query.

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. 

Teradata Employee

Re: Teradata Querydsl distinct limit query.

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