Hitta bruten sorteringsordning

Hej alla glada!

Är det någon som kan svara på om man i en procedur kan upptäcka om en nummerföljd är bruten. dvs. inte i nummerföljd.

Om man har en tabell med en sorteringsordning och vill se om nummer i den sorteringsordningen inte är i följd?

ex. 1,2,4,5

Procedurer vet jag inte mycket om, men här är en fråga som (förhoppningsvis) gör det du vill på ett bra sätt. Ersätt alla förekomster av tablename och colname med tabellens och kolumnens namn, så klart.

SELECT 
    t1.`colname` + 1 AS lowgap,
    (SELECT ti.`colname` - 1 FROM `tablename` ti WHERE ti.`colname` > t1.`colname` ORDER BY ti.`colname` LIMIT 1) AS highgap
FROM `tablename` t1
LEFT JOIN `tablename` t2 ON t2.`colname` = t1.`colname` + 1
WHERE t2.`colname` IS NULL AND 
    t1.`colname` != (SELECT MAX(`colname`) FROM `tablename`)

Kärnan i frågan är LEFT JOINen och t2.`colname` IS NULL. Det mönstret är allmänt användbart och letar efter JOINar som inte går igenom. LEFT JOIN (till skillnad från INNER JOIN) tillåter matchningen att misslyckas, och då blir det bara NULL av raden som joinas in, vilket alltså testas i WHERE. Den andra delen av WHERE, till höger om AND, utesluter den raden med det högsta värdet, som annars hade had uppfyllt kriteriet att den saknar en mostvarande rad med värdet+1.

Den delen av frågan ger dig bara alla värden som ligger före ett hål. Om det är allt du behöver så kan du ta bort allt i SELECT och ersätta med SELECT t1.`colname`.

Värdena i SELECT producerar det lägsta och högsta värdet i varje hål. För den lägre gränsen så är det helt enkelt t1.`colname`+1. För den övre gränsen så hämtar frågan ut nästa värde som finns och är högre, och tar det minus ett.

Det finns kanske mer eleganta eller snabbare sätt att utföra detta, men det här fungerar iaf.

Exempeldata:

Tabellen innehåller: 1,2,3,6,7,9,10

lowgap	highgap
4	5
8	8

Tack för det snabba svaret. Hittade även denna variant som jag testade...och fungerade:

Declare @m_TestTable table
(
DateRecorded datetime,
PointValue int
)

—Insert sample data
Insert into @m_TestTable Values (dateadd(day,1,GetDate()),150)
Insert into @m_TestTable Values (dateadd(day,2,GetDate()),350)
Insert into @m_TestTable Values (dateadd(day,3,GetDate()),500)
Insert into @m_TestTable Values (dateadd(day,4,GetDate()),100)
Insert into @m_TestTable Values (dateadd(day,5,GetDate()),150);

—Create CTE
With tblDifference as
(
Select Row_Number() OVER (Order by DateRecorded) as RowNumber,DateRecorded,PointValue from @m_TestTable
)

—Actual Query
Select convert(varchar, Cur.DateRecorded,103) as CurrentDay, convert(varchar, Prv.DateRecorded,103) as PreviousDay,Cur.PointValue as CurrentValue, Prv.PointValue as PreviousValue,Cur.PointValue-Prv.PointValue as Difference from  

tblDifference Cur Left Outer Join tblDifference Prv
On Cur.RowNumber=Prv.RowNumber+1
Order by Cur.DateRecorded

Om du vill kontrollera om en nummerföljd är bruten eller inte, är inte en FUNCTION bättre än en PROCEDURE då? Funktionen kan lätt fås att returnera antingen en bit eller, om du hellre vill det, tex, den (första) delen av nummerföljden så långt den är i följd.

Exempel från SQL SERVER, där en viss tabell alltid kollas (naturligtvis kan man skicka in sekvensen som indata istället, eller namnet på tabell och kolumn att kolla):

CREATE FUNCTION SequenceTest()
RETURNS bit
AS
BEGIN

  DECLARE @max int, @min int

  SELECT @max = MAX(TstColumn), @min = MIN(TstColumn) FROM MyTable

  IF @max = @min 
     RETURN 1 -- En sekvens på ett tal är hel

  IF EXISTS (
    SELECT
      1
    FROM
       MyTable F
       LEFT JOIN MyTable X ON
          F.TstColumn = X.TstColumn + 1 AND
          F.TstColumn < @max   
    WHERE
       X.TstColumn IS NULL  -- Givet att den inte kan vara NULL normalt, annars kolla om en PRIMARY KEY kolumn är NULL
  )
    RETURN 0
  ELSE RETURN 1

END

Returnerar 1 om sekvensen är hel och 0 annars.