I've been trying to figure out a way to order a row after another based on the value of a column from the previous row. In my SQL query below, I first initially want to order the dataset by distance in ascending order.
However, after that, I'd like to then check (on each row), whether the difference between the "distance" value of the previous row and the "distance" value of the current row is less than 50. If so, I'd then like to sort whichever row has highest "rating" value first.
Example: In the the SQL table values for "mytable" below, the 7th row has a "distance" value of 969, whereas the 6th row has a "distance" value of 949. The 7th row has a higher rating value (9) than the 6th's (1), so the 7th row should go before the 6th.
I've attempted to do this in the SQL query below, however, it seems to not produce the desired result:
WITH distance_sorted_rows AS (
SELECT id, name, website, ST_AsText(coordinates) AS coordinates, rating,
ST_Distance(ST_MakePoint(-2.91334, 53.38866)::geography, coordinates) AS distance
FROM spots WHERE ST_DWithin(ST_MakePoint(-2.91334, 53.38866)::geography, coordinates, 2000) ORDER BY distance ASC),
distance_rating_delta AS (
SELECT *,
ROUND(distance - LAG(distance) OVER (ORDER BY distance)) AS distanceDelta,
LAG(rating) OVER (ORDER BY distance) AS previousRating
FROM distance_sorted_rows)
SELECT * FROM distance_rating_delta ORDER BY
CASE WHEN (distanceDelta < 50 AND rating > previousRating) THEN
rating
END;
CREATE TABLE mytable(
id uuid,
name varchar,
website varchar,
coordinates geography,
rating float8,
distance float8,
distancedelta int,
previousrating float8
);
INSERT INTO mytable(id,name,website,coordinates,rating,distance,distancedelta,previousrating) VALUES ('5f979120-9e86-40e9-8482-26b2c1ce7ef4','Skaus','skaus.co.uk/','POINT(-2.911570499999999 53.388158)','2.0218843354875204',130.31440951,NULL,NULL);
INSERT INTO mytable(id,name,website,coordinates,rating,distance,distancedelta,previousrating) VALUES ('ba53daa4-4d73-459e-8449-73bbfb7b5174','The Tavern Co','tavernco.co.uk','POINT(-2.9163234 53.3890959)','4.265718759935204',204.33532344,74,2.0218843354875204);
INSERT INTO mytable(id,name,website,coordinates,rating,distance,distancedelta,previousrating) VALUES ('feb39664-1acf-4510-8db7-e22fdda93620','East River','east-river.co','POINT(-2.9076413 53.3856365)','0.4203535108919709',506.94650894,303,4.265718759935204);
INSERT INTO mytable(id,name,website,coordinates,rating,distance,distancedelta,previousrating) VALUES ('3a520259-9a2a-4694-a8c6-741ca4462dc3','The Dark Kitchen Presents',NULL,'POINT(-2.907002599999999 53.3850091)','9.995401744212913',585.57392164,79,0.4203535108919709);
INSERT INTO mytable(id,name,website,coordinates,rating,distance,distancedelta,previousrating) VALUES ('634013c8-765b-47d1-a18b-8e353f2d8af3','Istanbul',NULL,'POINT(-2.9066807 53.38427069999999)','1.4553719958512445',659.51257974,74,9.995401744212913);
INSERT INTO mytable(id,name,website,coordinates,rating,distance,distancedelta,previousrating) VALUES ('d3b998c5-6261-4657-8a11-cab10330b2ec','The Brookhouse','greatukpubs.co.uk/thebrookhouseliverpool','POINT(-2.927451 53.389939)','1.2668556701663647',949.55975863,290,1.4553719958512445);
INSERT INTO mytable(id,name,website,coordinates,rating,distance,distancedelta,previousrating) VALUES ('3a152c5c-5f8e-43c9-a22b-f1153fdb8106','Cucina di Vincenzo','cucinadivincenzo.co.uk','POINT(-2.8990017 53.3902205)','9.000527827897464',969.62995376,20,1.2668556701663647);
INSERT INTO mytable(id,name,website,coordinates,rating,distance,distancedelta,previousrating) VALUES ('9ff20bfb-f889-490d-8731-4f8a7f5deddd','Gustum','gustumbarandrestaurant.com/','POINT(-2.9144627 53.3795465)','9.232402539958144',1017.02058478,47,9.000527827897464);
INSERT INTO mytable(id,name,website,coordinates,rating,distance,distancedelta,previousrating) VALUES ('f6d0c482-6356-4c8c-8033-f6d36a7f2996','Little Furnace','littlefurnacepizza.wordpress.com/','POINT(-2.9320837 53.3914048)','2.3313195151308364',1283.90108214,267,9.232402539958144);
INSERT INTO mytable(id,name,website,coordinates,rating,distance,distancedelta,previousrating) VALUES ('2c71a01d-3ace-4cab-acbd-af96aa766a19','Belzan','belzan.co.uk','POINT(-2.9322491 53.3917837)','7.301193586670571',1305.18040239,21,2.3313195151308364);
INSERT INTO mytable(id,name,website,coordinates,rating,distance,distancedelta,previousrating) VALUES ('2f152f74-dfd1-47d8-8c24-1f3781bc6292','Caffee Oro',NULL,'POINT(-2.9331038 53.3924063)','9.244511694296769',1379.40395042,74,7.301193586670571);
INSERT INTO mytable(id,name,website,coordinates,rating,distance,distancedelta,previousrating) VALUES ('ff0bb383-6303-4102-ae03-2cc24ad39c7d','Elif','elifchildwall.co.uk/','POINT(-2.8975955 53.3968067)','9.907379232489504',1385.33794995,6,9.244511694296769);
