December 29, 2019 at 9:10 pm
Hi, Thanks in advance.
I have a datetime column in a table.
I want to run a query to get a true or false value.. true if the datetimes in the table are in date order, and false if they are not in date order.
Thank you.
December 29, 2019 at 11:15 pm
Hi SIJCOOKE,
If your table has a clustered index (one of these is created by default when a primary key is added to the table) on an auto-incrementing integer, then you should be able to do this with a modification of the below SELECT statement.
DECLARE @ORDERS TABLE (
ORDER_ID INT IDENTITY(1,1) PRIMARY KEY
, ORDER_DATE DATETIME
)
INSERT INTO @ORDERS (ORDER_DATE)
SELECT '20190101'
UNION ALL SELECT '20190202'
UNION ALL SELECT '20190303'
SELECT
CASE WHEN NOT EXISTS (
SELECT 1
FROM @ORDERS AS ORDERS
WHERE EXISTS (
SELECT 1
FROM @ORDERS AS PROBLEM_ORDER
WHERE PROBLEM_ORDER.ORDER_DATE > ORDERS.ORDER_DATE
AND PROBLEM_ORDER.ORDER_ID < ORDERS.ORDER_ID
)
) THEN 1 ELSE 0 END AS IN_DATE_ORDER
If there's no clustered index on the table, your table will be stored in a heap structure on disk, with no order to the rows stored on disk. If you don't know what a clustered index is, I recommend learning the basics about those to better help you understand what you're working with.
It could help give a more informative answer if you could describe what you're trying to achieve, and provide the definition of the table (the CREATE TABLE statement for this table).
Also, are you using Microsoft SQL Server, or a different SQL language? We might be able to provide more accurate help with that information.
Andrew
December 29, 2019 at 11:19 pm
A table doesn't store data in order. You can only guarantee getting data back in a particular order if you use an ORDER BY clause.
Is there another column that you order the table by? For example an ID column?
IF EXISTS(SELECT *
FROM myTable a
WHERE EXISTS(SELECT *
FROM myTable b
WHERE a.ID > b.ID
AND a.Date < b.Date))
SELECT 'False' InCorrectOrder
ELSE
SELECT 'True' InCorrectOrder
January 1, 2020 at 7:50 pm
This request makes absolutely no sense. The first property they should have taught you in your SQL class is that the rows in the table have no ordering! It models a set, not a sequence. Next, we don't like true and false flags in SQL. Part of that is because we have a three value logic (true, false, unknown), and because that's how we used to write in assembly language with flags. What are you trying to do? Do you want to see there's a row for every date in a given time interval?
Please post DDL and follow ANSI/ISO standards when asking for help.
January 4, 2020 at 9:06 pm
A table doesn't store data in order. You can only guarantee getting data back in a particular order if you use an ORDER BY clause.
usa today[/URL] protonmail[/URL]
Is there another column that you order the table by? For example an ID column?
IF EXISTS(SELECT *
FROM myTable a
WHERE EXISTS(SELECT *
FROM myTable b
WHERE a.ID > b.ID
AND a.Date < b.Date))
SELECT 'False' InCorrectOrder
ELSE
SELECT 'True' InCorrectOrder
how did you resolve your problem i have the same with my program too!
January 5, 2020 at 12:53 am
I want to run a query to get a true or false value.. true if the datetimes in the table are in date order, and false if they are not in date order. <<
> I want to run a query to get a true or false value.. true if the datetimes in the table are in date order, and false if they are not in date order. <<
The question is wrong in so many ways. You don't seem to understand that in the RDBMS model of data, the rows in the table have no ordering. This is usually covered in the first few hours of any course in RDBMS or SQL.
A few days after that, you would realize that using true and false flags belong in assembly language, not in SQL. SQL is based on predicates that return sets of rose, not assembly language flags.
By the next week or so, you would have studied Dr. Codd's "information principle" which states that all relationships (such as ordering) have to be modeled by scalar values in the columns of the rows of a table.
This old posting of mine might be helpful for what I am guessing you are trying to do:
https://www.red-gate.com/simple-talk/sql/t-sql-programming/state-transition-constraints/
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply