Hi, Can some one explain me how to optimise this query?

select disticnt(csuser.id), csuser.loginName,csuser.displayName,loginSession.l astlogindatetime,loginSession.activesessions,usert ypes.csusertypeid from User csuser join userroleassign csuserroleassign on csuserroleassign.roleid=52 left outer join userloginsession csloginSession on csloginSession.userid=csuser.id left outer join userusertypeassign usertypes on usertypes.userid=csuser.id limit 15;

If I don't add that distcint keyword, query is returning results in 18 milli seconds; but if I add distcinct it did not return results in two miunutes and finally I have to stop the exection of the query.

Interestingly, if I say select distinct(user.id) from user; then it is pretty quick and returning in 15 milliseconds.

Sorry for asking such a basic question but I'm not that good at SQL.

Thanks and Regards,
Samba

DISTINCT is ~not~ a function

if you put a column in parentheses after DISTINCT, that does not change what DISTINCT does -- DISTINCT applies to all columns in the SELECT clause

DISTINCT is implemented by a sort on the result set, sorting on all columns, then looking for duplicate rows, i.e. duplicate in all columns

thanks for the clarification

Did you miss a join condition on the userroleassign csuserroleassign table? Your example shows the only limiting criteria on that table is the role id being a 52. Without join criteria to your other tables this would create a cartesian product, which could explain the length of time it takes to sort all rows. Also, when you say data is returned in 18 milliseconds without the distinct, is that all rows or just a first subset of the rows?
Dave

Hi Dave,
I meant that the first subset of data is returned in around 18 millisconds if I don't say distinct, which obviously leaves out some records which do not in the subset.

what I have:

User*----*Role
User-----*loginsession
User----*UserType
User-------*Profile
defaultProfile-----*Address
Address----phone

here userroleassign and userusertypeassign are the jooin tables for many to many associations between user==>role and user==>usertype respectively.

Now I want some fields from user,some fields from usertype,some fields from
loginSession,and phone number from his defaultprofile-->adresss where user has a certain role.

I could not come up with a query that gives me the above explained data set.

Can you help build a query that gets the above resultset?

Thanks and Regards,
Samba

I'm afraid it is not true. Distinct do not have to aplly to all columns, if you use
then distinct will apply only to those column you write in parentheses

yes, but....my advice is to stay away from non-standard SQL unless there is a real good reason

and this aint one of them

OK, but you can't say that
cos in postgres it is not true .

it is so true!!!

you are getting confused

in postgresql, DISTINCT ON and DISTINCT are two completely separate things

i was talking about putting an expression into parentheses after the DISTINCT

DISTINCT ON is completely different semantics

You are of course right