I have a column with names of spots and different XY coordinates. I am trying to get the max(X), max(Y), min(X), min(Y) for distinct spots names. However, for each min/max coordinate, I want to get its corresponding X or Y. Here is the pedInfo table:

I wrote the following query to get the MAX/MIN X/Y values, but I am not sure how to get the corresponding X/Y for each of them.

SELECT DISTINCT(AtSpot), MAX(X) AS max_X, MAX(Y) AS max_Y, MIN(X) AS min_X, MIN(Y) AS min_Y 
FROM pedInfo GROUP BY AtSpot;

CodePudding user response：

Try to group by X and Y after your selects.

SELECT DISTINCT(AtSpot), X, Y, MAX(X) AS max_X, MAX(Y) AS max_Y, MIN(X) AS min_X, 
MIN(Y) AS min_Y 
FROM pedInfo GROUP BY AtSpot, max_X, min_X;

CodePudding user response：

I can only see this possible as long as X and Y values are not repeated for any spot. I have two approaches to consider.

1. First step is to build aggregate query but without DISTINCT as it serves no purpose. Next, build another query that has 4 instances of table and one of each will INNER JOIN to aggregate query with compound linking on AtSpot fields and one of the aggregate fields to appropriate X or Y field. Following is the second query:

SELECT qMaxMin.AtSpot, qMaxMin.max_X, pedInfo.y, pedInfo_1.x, pedInfo_1.y, pedInfo_2.x, pedInfo_2.y, pedInfo_3.x, pedInfo_3.y
FROM pedInfo AS pedInfo_3 
INNER JOIN (pedInfo AS pedInfo_2 
INNER JOIN (pedInfo AS pedInfo_1 
INNER JOIN (pedInfo 
INNER JOIN qMaxMin 
ON (pedInfo.x = qMaxMin.max_X) AND (pedInfo.AtSpot = qMaxMin.AtSpot)) 
ON (pedInfo_1.y = qMaxMin.max_Y) AND (pedInfo_1.AtSpot = qMaxMin.AtSpot)) 
ON (pedInfo_2.x = qMaxMin.min_X) AND (pedInfo_2.AtSpot = qMaxMin.AtSpot)) 
ON (pedInfo_3.y = qMaxMin.min_Y) AND (pedInfo_3.AtSpot = qMaxMin.AtSpot);

2. Second approach requires a unique identifier field such as autonumber. Build four TOP N nested queries. Build a 5th query that uses one of the 4 as 'parent' and joins the other 3 on AtSpot fields. This method may be slower with large dataset. Following are the 4 TOP N queries:

qMaxX

SELECT * FROM pedINFO WHERE ID IN (
  SELECT TOP 1 ID FROM pedInfo AS Dup WHERE Dup.AtSpot = pedInfo.AtSpot ORDER BY Dup.X DESC);

qMaxY

SELECT * FROM pedINFO WHERE ID IN (
  SELECT TOP 1 ID FROM pedInfo AS Dup WHERE Dup.AtSpot = pedInfo.AtSpot ORDER BY Dup.Y DESC);

qMinX

SELECT * FROM pedINFO WHERE ID IN (
  SELECT TOP 1 ID FROM pedInfo AS Dup WHERE Dup.AtSpot = pedInfo.AtSpot ORDER BY Dup.X);

qMinY

SELECT * FROM pedINFO WHERE ID IN (
  SELECT TOP 1 ID FROM pedInfo AS Dup WHERE Dup.AtSpot = pedInfo.AtSpot ORDER BY Dup.Y);