How to order sql query results based on attribute values AND distance from features?

[cesare gerbino]

I've the follwing SQL statement

SELECT distr.Comune, distr.Gestore, distr.Indirizzo, distr.Bandiera, prz.descCarburante, prz.prezzo

    FROM anagrafica_impianti_attivi as distr

    JOIN prezzo_alle_8 as prz ON (prz.idImpianto = distr.IdImpianto) WHERE

    PtDistWithin(distr.geometry, MakePoint(13.571386, 37.314926, 4326), 1000) ORDER BY prz.prezzo ASC;

that works fine in my Spatialite data base.

I'd like to order my results not only based on the values of prz.prezzo in ASC mode, but also based on the nearest distance of that points from the search point MakePoint(13.571386, 37.314926, 4326) because if I've several features with the same prz.prezzo value I'd like to show the nearest first and so on ..... 

Any suggestion will be appreciated .. Thank you in advance!!!

 Cesare

[a.fu...@lqt.it]

Hi Cesare,

you simply have to rewrite your query in a slightly different form;
BTW I imagine that returning the relative distance (in metres) for
each row of the resultset should be a good idea:

SELECT distr.Comune, distr.Gestore, distr.Indirizzo,
distr.Bandiera, prz.descCarburante, prz.prezzo,
ST_Distance(distr.geom,
MakePoint(13.571386, 37.314926, 4326), 1) AS dist

FROM anagrafica_impianti_attivi as distr
JOIN prezzo_alle_8 as prz ON (prz.idImpianto = distr.IdImpianto)

WHERE dist <= 1000
ORDER BY prz.prezzo ASC, dist ASC;

note: even if your geometries are based on geographic coords
(long/lat), ST_Distance(a, b, 1) will return a distance
expressed in metres and precisely measured on the ellipsoid
by applying the most accurate geodetic formulae.

bye Sandro

[cesare gerbino]

Ok, it's working now ... thank you Sandro!