Compare Values for Rows

Question

DECLARE @Comparison TABLE

(
  ID int identity (1,1) primary key,
  ComparisonID int,
   Value int
)

INSERT INTO @Comparison
SELECT 11, 1
INSERT INTO @Comparison
SELECT 13, 1
INSERT INTO @Comparison
SELECT 17, 2
INSERT INTO @Comparison
SELECT 22, 1

INSERT INTO @Comparison
SELECT 11, 2
INSERT INTO @Comparison
SELECT 13, 3
INSERT INTO @Comparison
SELECT 13, 4
INSERT INTO @Comparison
SELECT 17, 22
INSERT INTO @Comparison
SELECT 22, 12

 ;With X
As
(
SELECT ID, ComparisonID, [Value], Row_Number() Over(partition by ComparisonID Order by ComparisonID) As RN
FROM @Comparison
)

Select 
ID,
RN,
ComparisonID

  , [Value]
  , (Select Case When Y.[VALUE] is Null Then Null
     When Y.[Value] = X.[VALUE] Then 'Same'
     When Y.[Value] < X.[VALUE] Then 'High'
     When Y.[Value] > X.[VALUE] Then 'Low' End
   From X Y
   Where X.RN = Y.RN + 1
   and X.ComparisonID = Y.ComparisonID
  ) As Comparison
From  X
order by ComparisonID, ID

The SQL needs to do the below, but I am facing one issue:

The first row of a specfic ComparisonID (11) will be NULL because there is nothing to compare it against.
The second row the value is 2 and the value for the row above is 1 so I would like to show "High" in the new column

The first row of a specfic ComparisonID (13) will be NULL because there is nothing to compare it against.
The second row the value is 4 and the value for the row above is 3 so I would like to show "High" in the new column
The third row the value is 3 and the value for the row above is 4 so I would like to show "Low" in the new column

Etc

ID RN ComparisonID Value Comparison
1   1    11                  1        NULL
5   2    11                  2        High
2   3    13                  1        Low
6   1    13                  3        NULL  -----INCORRECT
7   2    13                  4        High  -----INCORRECT
3   1    17                  2        NULL
8   2    17                  22      High
4   2    22                 1         Low   -----INCORRECT
9   1    22                  12      NULL  -----INCORRECT

It should be the below: It works for some comparison ie Comparison ID 11 is working but for ComparisonID 13 and 22 it is not. It seems like the RN numbers are in the incorrect order. I am not sure what is going wrong here, please help.

ID RN ComparisonID Value Comparison
1   1    11                  1        NULL
5   2    11                  2        High
6   1    13                  3        NULL 
7   2    13                  4        High 
2   3    13                  1        Low
3   1    17                  2        NULL
8   2    17                  22      High
9   1    22                  12      NULL 
4   2    22                 1         Low  

Answer 1

E.g. like this, but you need another ORDER BY in the ROW_NUMBER() ranking function:

WITH Ordered AS 
	(
		SELECT	ID, 
				ComparisonID, 
				[VALUE], 
				ROW_NUMBER() OVER(PARTITION BY ComparisonID ORDER BY ID) AS RN
		FROM	@Comparison
	)
	SELECT	L.ID, 
			L.ComparisonID, 
			L.[Value], 
			CASE	WHEN R.[value] IS NULL THEN NULL 
					WHEN L.[Value] > R.[Value] THEN 'high' 
					WHEN L.[VALUE] = R.[VALUE] THEN 'same'				
					ELSE 'low' 
			END
	FROM	Ordered L
		LEFT JOIN Ordered R ON L.ComparisonID = R.ComparisonID AND L.RN = R.RN + 1;

Answer 2

You use the term "The first row of a specfic ComparisonID", what do you mean by the "first row"?  Do you mean the row with the lowest ID value for that ComparisonID?  If so, then you could use

 ;With X
As
(
SELECT ID, ComparisonID, [Value], Row_Number() Over(partition by ComparisonID Order by ID) As RN
FROM @Comparison
)

Select 
ID,
RN,
ComparisonID

  , [Value]
  , (Select Case When Y.[VALUE] is Null Then Null
     When Y.[Value] = X.[VALUE] Then 'Same'
     When Y.[Value] < X.[VALUE] Then 'High'
     When Y.[Value] > X.[VALUE] Then 'Low' End
   From X Y
   Where X.RN = Y.RN + 1
   and X.ComparisonID = Y.ComparisonID
  ) As Comparison
From  X
order by ComparisonID, ID 

Please note that that does not actually give what you said you wanted as the result.  But if you really want that to be the result, you need to explain why (for example) you want the row with ComparisonID = 13 and ID = 6 to be RN = 1, and the row with ComparisonID = 13 and ID = 7 to be RN = 2, and the row with ComparisonID = 13 and ID = 2 to be RN = 3.

Tom

Answer 3

from your explanation, I think you need to add the order by comparisonid, rn, as below:

DECLARE @Comparison TABLE

(
  ID int identity (1,1) primary key,
  ComparisonID int, 
   Value int
)

INSERT INTO @Comparison
SELECT 11, 1
INSERT INTO @Comparison
SELECT 13, 1
INSERT INTO @Comparison
SELECT 17, 2
INSERT INTO @Comparison
SELECT 22, 1

INSERT INTO @Comparison
SELECT 11, 2
INSERT INTO @Comparison
SELECT 13, 3
INSERT INTO @Comparison
SELECT 13, 4
INSERT INTO @Comparison
SELECT 17, 22
INSERT INTO @Comparison
SELECT 22, 12

 ;With X
As
(
SELECT ID, ComparisonID, [Value], Row_Number() Over(partition by ComparisonID Order by ComparisonID) As RN
FROM @Comparison
)

Select  
ID, 
RN, 
ComparisonID

  , [Value]
  , (Select Case When Y.[VALUE] is Null Then Null 
     When Y.[Value] = X.[VALUE] Then 'Same'
     When Y.[Value] < X.[VALUE] Then 'High'
     When Y.[Value] > X.[VALUE] Then 'Low' End
   From X Y
   Where X.RN = Y.RN + 1
   and X.ComparisonID = Y.ComparisonID
  ) As Comparison
From  X
order by ComparisonID, RN

Answer 4

You might want to read any book on data modeling and RDBMS. There is no generic “id”, tables have names and keys. IDENTITY is a count in the hardware and has no place in RDBMS. Logically, it is the record count of a mag tape and not a key. Looking at your postings you are not writing SQL yet; you are writing some procedural language in SQL because your mindset is still back in tape files. 

My guess would be that you meant this:

CREATE TABLE Foobar
(foo_id INTEGER NOT NULL,
 vague_value INTEGER NOT NULL,
 PRIMARY KEY (foo_id, vague_value));

Another reason IDENTITY sucks is that it is unpredictable when you insert a set of more than one row. 


INSERT INTO Foobar
VALUES
(11, 1), 
(13, 1), 
(17, 2), 
(22, 1), 
(11, 2), 
(13, 3), 
(13, 4), 
(17, 22), 
(22, 12);


You need to keep up with your reading. We have more window functions now: 


SELECT foo_id, vague_value AS current_vague_value, 
       LAG(vague_value)
         OVER (PARTITION BY foo_id ORDER BY foo_id, vague_value) 
       AS prior_vague_value,
       MIN(vague_value) OVER (PARTITION BY foo_id) 
       AS lowest_vague_value,
       MAX(vague_value) OVER (PARTITION BY foo_id) 
       AS highest_vague_value   
  FROM Foobar
  ORDER BY foo_id, current_vague_value; 

>> The first row of a specific foo_id (11) will be NULL because there is nothing to compare it against. The second row the vague_value is 2 and the vague_value for the row above is 1 so I would like to show "High" in the new column <<

Rows have no physical ordering; you still think in sequential files and not tables. My guess from your narrative is that you want to flag the highest and lowest vague_values in each foo_id. If you still want those flags, write a CASE expression using the columns I have in that query. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL