CODEWITHSUNDEEP
mysqlsqlms-access-2007
Richard Robertson
Richard Robertson

Reputation: 149

Producing multiple maximum and minimum values with SQL Query

I'm becoming frustrated with a curious limitation of SQL - its apparent inability to relate one record to another outside of aggregate functions. My problem is summarized thusly. I have a table, already sorted. I need to find its maximum values (note the plural!) and minimum values. No, I am not looking for a single maximum or single minimum. More specifically I'm trying to generate a list of the local peaks of a numeric sequence. A rough description of an algorithm to generate this is:

WHILE NOT END_OF_TABLE
 IF RECORD != FIRST_RECORD AND RECORD != LAST_RECORD THEN
  IF ((RECORD(Field)<RECORD_PREVIOUS(Field) AND RECORD(Field)<RECORD_NEXT(Field)) OR
      RECORD(Field)>RECORD_PREVIOUS(Field) AND RECORD(Field)>RECORD_NEXT(Field)) THEN
     ADD_RESULT RECORD
  END IF
 END IF
END WHILE

See the Problem? I need to do a query that a given record must compare against the previous and next records' values. Can this even be accomplished in standard SQL?

Upvotes: 1

Views: 1618

Answers (4)

MatBailie
MatBailie

Reputation: 86735

SELECT
  current.RowID,
  current.Value,
  CASE WHEN
    (
     (current.Value < COALESCE(previous.Value,   current.Value + 1))
     AND
     (current.Value < COALESCE(subsequent.Value, current.Value + 1))
    )
  THEN
    'Minima'
  ELSE
    'Maxima'
  END
FROM
  myTable  current
LEFT JOIN
  myTable  previous
    ON previous.RowID = (SELECT MAX(RowID) FROM myTable WHERE RowID < current.ROWID)
LEFT JOIN
  myTable  subsequent
    ON subsequent.RowID = (SELECT MIN(RowID) FROM myTable WHERE RowID > current.ROWID)
WHERE
  (
   (current.Value < COALESCE(previous.Value,   current.Value + 1))
   AND
   (current.Value < COALESCE(subsequent.Value, current.Value + 1))
  )
  OR
  (
   (current.Value > COALESCE(previous.Value,   current.Value - 1))
   AND
   (current.Value > COALESCE(subsequent.Value, current.Value - 1))
  )

Note: The < and > logic is copied from you, but does not cater for local maxima/minima that are equal across one or more consecutive records.

Note: I've created a fictional RowID to join the records in order, all the is important is that the joins get the "previous" and "subsequent" records.

Note: The LEFT JOINs and COALESCE statements cause the first and last values to always be counted as a maxima or minima.

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89671

You have to self-join twice and generate a rownumber without gaps:

In T-SQL:

WITH ordered AS (
    SELECT ROW_NUMBER() OVER (ORDER BY your_sort_order) AS RowNumber
           ,* -- other columns here
)
SELECT *
FROM ordered
LEFT JOIN ordered AS prev
    ON prev.RowNumber = ordered.RowNumber - 1
LEFT JOIN ordered AS next
    ON next.RowNumber = ordered.RowNumber + 1
WHERE -- here you put in your local min/local max and end-point handling logic - end points will have NULL in next/prev

Upvotes: 2

Adam Robinson
Adam Robinson

Reputation: 185643

Your frustration is shared by many; while SQL is great for working with general sets, it's terribly deficient when trying to work with issues specific to ordered sets (whether it's physically ordered in the table or there is an implicit or explicit logical order is irrelevant). There are some things that can help (for example, the rank() and row_number() functions), but the solutions can differ across RDBMS's.

If you can be specific about which platform you're working with, I or someone else can provide a more detailed answer.

Upvotes: 2

Neville Kuyt
Neville Kuyt

Reputation: 29629

Yes. You need a self join - but without a database schema, it's hard to be specific about the solution.

Specifically, I'm wondering about the "ordering" thing you mention - but I'm going to assume there's an "ID" field we can use for this.

(Oh, and I'm using old-school join syntax, coz I'm a dinosaur).

select *
from   myTable   main,
       myTable   previous,
       myTable   next
where  previous.id  = main.id - 1
and    next.id      = main.id + 1
and    previous.record > main.record
and    next.record     < main.record

(I think I've interpreted your requirement correctly in the greater/less than clauses, but adjust to taste).

Upvotes: 1

Related Questions