0

I wrote a query to create a table with one column

CREATE TABLE ORDER( 
   Order_Date DATE NOT NULL
)

I inserted two values into the column

--Order_Date--
2018-05-21
2018-01-21

How to check if the first row is greater or less than the second row using IF statement?

Israel Obanijesu
  • 666
  • 1
  • 12
  • 24
  • to compare using `if` you should store the values in variables and compare like this `IF @firstDate > @secondDate` – Ricardo Pontual Jun 21 '18 at 11:30
  • how can I get the value of each row, pls can you tell me? I don't know how to do that in a trigger – Israel Obanijesu Jun 21 '18 at 11:37
  • are you trying to do it in a trigger? it won't be impossible to know the last two or three inserted records, you can know only the last. I sugest you to include a identity column in order to use `order by` clause to get the last inserted rows – Ricardo Pontual Jun 21 '18 at 11:43

1 Answers1

3

SQL tables represent unordered sets. There is no "first" row or "second" row in a table.

You can do what you want if you include an identity column:

CREATE TABLE ORDERS ( 
   OrderId IDENTITY(1, 1) PRIMARY KEY,
   Order_Date DATE NOT NULL
);

Now, you can see if OrderId and OrderDate are in the same order. Something like:

select (case when max(case when seqnum = 1 then order_date end) >
                  max(case when seqnum = 2 then order_date end)
             then 'greater than'
             when max(case when seqnum = 1 then order_date end) =
                  max(case when seqnum = 2 then order_date end)
             then 'equal'
             when max(case when seqnum = 1 then order_date end) <
                  max(case when seqnum = 2 then order_date end)
             then 'less than'
        end) as comparison
from (select o.*, row_number() over (order by OrderId) as seqnum
      from orders o
     ) o;

Notice that I also renamed the table to Orders. Order is a really bad name for a table because it is a SQL keyword and a reserved word as well.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I tried your query on my laptop but is not working, pls can you show me the output of your query – Israel Obanijesu Jun 21 '18 at 12:18
  • My SSMS returned an error "Incorrect syntax near the keyword 'then'". The problem might be because I don't know how to manipulate your query – Israel Obanijesu Jun 21 '18 at 12:59
  • @IsraelObanijesu . . . There should be a `case` in those expressions. – Gordon Linoff Jun 22 '18 at 02:01
  • Your query is working but how can I use this to manage series of IDs. Is it possible for someone to check for the IDs in an automatic way without declaring it manually?? – Israel Obanijesu Jun 23 '18 at 03:56
  • @IsraelObanijesu . . . As far as I can tell, this answer answers this question. If you have another question, ask it as a question. Include sample data, desired results, and an explanation of what you want to accomplish. – Gordon Linoff Jun 23 '18 at 12:25