SQL query which find different values on one column in the same table

Question

I have the following table where Description field had scope = 10 and I inserted first row with Description = Cobra - Ni but a few days ago I realized that I need larger scope which was extended and proper value should be Cobra - Nisyor.

Column_ref     Description     Date          Money     Doc_nr
123            Cobra - Ni      06-11-2015    505.50    2000/10
123            Cobra - Toung   07-11-2015    505.50    2000/12
123            Cobra - Brain   07-11-2015    505.50    2000/25
123            Cobra - Nisyor  07-11-2015    505.50    2000/10

I need to write query which find first and last row from this exemplary table.

I tried in this way:

SELECT t1.*
FROM table as t1
WHERE t1.Description in
      (SELECT t2.Description
       FROM table as t2
       WHERE t1.Doc_nr = t2.Doc_nr
       AND t1.Description != t2.Description)

but it doesn't work.

You specifically search for items that have a *different* description, and then you expect it to be the same? — Luaan, Nov 06 '15 at 15:15
I.e. when several rows for the same date, how do you chose first/last? — jarlh, Nov 06 '15 at 15:15
Are you looking for rows with the same doc_nr but different descriptions? — Adish, Nov 06 '15 at 15:18
Based on your requirements and provided data: `WHERE Doc_nr = '2000/10'` — Michael McGriff, Nov 06 '15 at 15:19
@Adish: Yes, I'm looking for rows with the same doc_nr but different description column — Merix, Nov 06 '15 at 15:44
@MichaelMcGriff: I have more rows with this issues so I should use Doc_nr field in where clause. — Merix, Nov 06 '15 at 15:44
What do you want to do after you get rows with same doc_nr and different descriptions? — Adish, Nov 06 '15 at 16:04
In that case the delete solution provided by Michael McGriff will work for you. — Adish, Nov 06 '15 at 16:36

score 1 · Accepted Answer · answered Nov 06 '15 at 15:17

I am assuming by "scope" you mean the width of the column was 10. Therefore you are looking to correlate rows, one having length = 10 and another starting with the same string and having length > 10. We can use the LEN() function to get the length of a character field, and LEFT() to get a substring - the latter of which we can use to compare the "new" with the "old".

For example:

with oldRows as (
    select *
    from myTable
    where LEN(Description) = 10
), newRows as (
    select *, LEFT(Description, 10) as oldKey
    from myTable
    where LEN(Description) > 10
)
select n.*, o.*
from oldRows o
join newRows n on o.Description = n.oldKey
-- Of course add any other comparisons you need to correlate rows:
--    and o.Column_ref = n.Column_ref
--    and o.[Date] = n.[Date]
--    and o.[Money] = n.[Money]
--    and o.Doc_nr = n.Doc_nr

For future reference you probably shouldn't have inserted additional new rows into the table after realizing the problem, and should have used an update instead.

score 0 · Answer 2 · edited May 23 '17 at 12:23

To find the rows you're looking for you need to do a self join on doc_nr including only those rows where the descriptions do not match, SQL Fiddle.

CREATE TABLE basic
(
  column_ref INT,
  description VARCHAR(30),
  dateField DATETIME,
  amount DECIMAL(12,2),
  doc_nr VARCHAR(30)
);

INSERT INTO basic (column_ref, description, dateField, amount, doc_nr)
VALUES (123, 'Cobra - Ni', '06/11/2015',505.50,'2000/10'),
       (123, 'Cobra - Toung', '07/11/2015',505.50,'2000/12'),
       (123, 'Cobra - Brain', '07/11/2015',505.50,'2000/25'),
       (123, 'Cobra - Nisyor', '07/11/2015',505.50,'2000/10');

SELECT *
FROM basic b
JOIN basic q ON b.doc_nr = q.doc_nr
WHERE b.description != q.description

╔════════════╦════════════════╦════════════════════════╦════════╦═════════╦════════════╦════════════════╦════════════════════════╦════════╦═════════╗
║ column_ref ║  description   ║       dateField        ║ amount ║ doc_nr  ║ column_ref ║  description   ║       dateField        ║ amount ║ doc_nr  ║
╠════════════╬════════════════╬════════════════════════╬════════╬═════════╬════════════╬════════════════╬════════════════════════╬════════╬═════════╣
║        123 ║ Cobra - Ni     ║ June, 11 2015 00:00:00 ║ 505.5  ║ 2000/10 ║        123 ║ Cobra - Nisyor ║ July, 11 2015 00:00:00 ║ 505.5  ║ 2000/10 ║
║        123 ║ Cobra - Nisyor ║ July, 11 2015 00:00:00 ║ 505.5  ║ 2000/10 ║        123 ║ Cobra - Ni     ║ June, 11 2015 00:00:00 ║ 505.5  ║ 2000/10 ║
╚════════════╩════════════════╩════════════════════════╩════════╩═════════╩════════════╩════════════════╩════════════════════════╩════════╩═════════╝

In order to actually DELETE the rows, replace the SELECT statement above with the following (provided you want to DELETE the rows which have the shorter description, your actual criteria may vary).

DELETE b 
FROM basic b
JOIN basic q ON b.doc_nr = q.doc_nr
WHERE LEN(b.description) < LEN(q.description);

Credit for the above syntax here.

Your answer for sure is correct. I checked it on SQL Fiddle and it works. I suppose something is wrong with my data but i know that your solution is also correct. Many thanks for SQL Fiddle, i didn't know this before! — Merix, Nov 06 '15 at 17:54

SQL query which find different values on one column in the same table

2 Answers2