How to make a distinct of one column in advanced query?

How to make a distinct of one column in advanced query?

  
How to make a distinct of one column in advanced query?

I can't modify the order of the columns in advanced query, so how to make a distint of one column.
Example:
TABLE1 contains Id and Xpto. I intend to make a distinct by Xpto.
SELECT DISTINCT {TABLE1}.[Xpto],{TABLE1}.[Id]
From {TABLE1}

Regards, Rui Dias
Hi Rui,

When you use the DISTINCT or the GROUP BY clause in the SQL query you'll have to keep in mind that what it is doing is aggregating a set of rows by the field(s) you indicate. In your example you want to aggregate by XPTO, that's fine. But now you have a set of rows that match this clause, from this set what value of Id do you want to retrieve? This is why if you use the group by like this:
SELECT {TABLE1}.[XPTO], {TABLE1}.[Id] FROM {TABLE1} GROUP BY {TABLE1}.[XPTO]
you'll receive an error indicating that {TABLE1}.[Id] is not an aggregate function.

Imagine you have
Id XPTO
1 1
2 1
3 2
4 1

Ok. So now you aggregate on XPTO. For the value of 2 in XPTO you only have one value of Id but for the value 1 you have three values for the Id. One common usage for this is to use a count function. Say you want to know how many records exist for each distinct value of XPTO. Then you'd use:
SELECT {TABLE1}.[XPTO], count(1) FROM {TABLE1} GROUP BY {TABLE1}.[XPTO]
Or imagine you'd like to know what is the highest value of ID for each distinct XPTO value. Then you'd use:
SELECT {TABLE1}.[XPTO], MAX({TABLE1}.[Id]) FROM {TABLE1} GROUP BY {TABLE1}.[XPTO]

I hope this was helpful.
If you need more help, please post a more concrete example of what you want to do.
Cheers,
André

Hi André,

I perceived group by, thanks for the explanation. What I really intend is the return of the columns of two tables but without rowns repeated in determined column.

Regards, Rui Dias
Rui,

Bear in mind that the same thoughts apply when you are joining tables. If you still need help please post your query here so that I can help you further.
Cheers,
André
Hi,

I intend to make this query:

SELECT Distinct XPTO.a
FROM (
SELECT A.a
From B RIGHT OUTER JOIN A ON B.b = A.a
Where
(
B.b = A.a
)
)As XPTO

I can´t use this query with AS in advanced query. Another solution is ?

Thanks.
Regards,
Rui Dias
Hi Rui,

I think that you're a bit confused. When you use the ON condition you do not have to add it to the WHERE part of the SQL sentence. I think that the query you want is something like this:

SELECT DISTINCT A.a FROM A LEFT OUTER JOIN B on A.a = B.b

or

SELECT DISTINCT A.a FROM B RIGHT OUTER JOIN A on A.a = B.b

or

SELECT A.a FROM A LEFT OUTER JOIN B ON A.a = B.b GROUP BY A.a

To create an alias you do not need to use 'AS' for instance:

SELECT XPTO.columnA FROM
(SELECT DISTINCT A.a columnA FROM B RIGHT OUTER JOIN A ON B.b = A.a) XPTO

should also be valid although it's for sure much more complex and harder to read then any of the previous versions.
Cheers,
André
I would also add that if you want to retrieve only one attribute from an entity, you should define a structure with a compatible layout and set this structure as the output of the advanced query.

Cheers,
Paulo Ramos
Hi,

Problem resolved. Thank you.


Regards,

Rui Dias
Hi,
      How can i use the distinct keyword  in standerd query.

thanks
Hi Rajendra,

You can only use DISTINCT in advanced query.