May 5, 2006 at 8:35 am
I have a table with some data that should be inserted and look like this..
ID Question Type value
(int) (int) (int) varchar(10)
-------------------------------
1 1111 100 Mytext
2 1111 101 5(c)
3 1112 100 Mytext
4 1112 101 5(c)
Sometimes however it ends up backwards like this
ID Question Type value
(int) (int) (int) varchar(10)
-------------------------------
1 1111 101 5(c)
2 1111 100 Mytext
I personally beleive this to be a poorly written data input routine in the application and I know that's primarily where I need to look to fix this, but I also need to find all of the records that are incorrect so that I can fix them. Does anyone out there have any ideas how this might be accomplished? I'm at somewhat of a loss. Thanks in advance.
-Luke.
May 5, 2006 at 8:59 am
Do you mean out of sequence as in Type is not ascending for a question?
If so this will list the entries that are out of sequence
SELECT a.*
FROM @table a
WHERE EXISTS (SELECT * FROM @table b WHERE b.Question = a.Question AND b.ID < a.ID AND b.Type > a.Type)
OR EXISTS (SELECT * FROM @table b WHERE b.Question = a.Question AND b.ID > a.ID AND b.Type < a.Type)
Far away is close at hand in the images of elsewhere.
Anon.
May 5, 2006 at 9:22 am
May 8, 2006 at 6:13 am
Yes, I understand that a simple order by in the SQL statement would be a simple fix for this. What I was really trying to find was how many rows were affected by this particular issue with the application and also find out how long it had been happening becasue of a datecreated and datemodified fields. Management asked for this information, so I have to provide it for them. They wanted to know the why and how many before they would accept the fact that the previous dba, and the contractor developer screwed them.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply