Hi guys, I have a query that req 1

[junket]

Hi guys,

I have a query that requires distinct records returned. These records need to be ordered by how close their postcode is to a fixed value, hence:

SELECT DISTINCT.....ORDER BY (ABS(3000-g.PostalCode))

(3000 is the fixed value - Aussie postcodes are generally 4 digits)

I continue to get the following error:

ORDER BY clause (ABS(3000-g.PostalCode)) conflicts with DISTINCT.

Is there a way around this?

Cheers
Matt

 

[DanChard]

Try getting rid of the order by in your initial query and then putting:

select * from (

before your query and:

) order by x;

after your query (where x is the column number of the column you want to order by).

Good luck,
Dan

 

[TonyJollans]

Hi junket,

You need to include your expresion in the selection ..

SELECT DISTINCT ... ,ABS(3000-g.PostalCode) AS DUMMY ... ORDER BY (ABS(3000-g.PostalCode))


Enjoy,
Tony

 

[junket]

Thanks guys,

I ended up using Tony's suggestion. I had tried possibly every combination of things except that.

Cheers
Matt