2

Could someone tell me how to make an SQL query that selects all rows where at least 1 of 4 columns is different from the previous rows. I want the same you do with a GROUP only then affecting multiple columns. When I use:

SELECT * FROM TABLE GROUP BY col1, col2, col3, col4

I don't get the result I want because the columns are grouped after each other

col1  col2  col3  col4 
1     2     2     4
1     2     2     3   <-- different -> select
1     2     3     2   <-- different -> select
1     2     3     2   <-- don't select
2     2     3     4   <-- different -> select

I don't want the 1 2 3 2 column 2 times and decide which one top pick based on a 5th column

-- Edit --

Sample data SQLfiddle: http://sqlfiddle.com/#!2/efd92e In this sample I want these rows returned

insert into tablename VALUES ("T", 12, 1000, 0, 500, 20.25); (4)*
insert into tablename VALUES ("H", 12, 150, 2500, 100, 45);  (0)*
insert into tablename VALUES ("H", 12, 100, 2500, 100, 45);  (2)*

*if it is possible, I also would like to know the number rows that not selected because they are "duplicated".

I Tried to write an query based on one of the awnser but no success yet

-- Clarification --

  • The combination of the 5 cols has to be unique.
  • I need the row of the duplicates with the lowest col6.
  • I want to know how many duplicates of this row are in the database

Thanks in advance for the help

Jab
  • 821
  • 3
  • 13
  • 26
  • 2
    possible duplicate of [How do I (or can I) SELECT DISTINCT on multiple columns?](http://stackoverflow.com/questions/54418/how-do-i-or-can-i-select-distinct-on-multiple-columns) – scrowler Dec 04 '13 at 21:23
  • 2
    You should rephrase your question. You want to select the rows where at least one of the columns has changed since **the previous row**. You also need to describe how the order of the rows is determined. – AgRizzo Dec 04 '13 at 21:25
  • user1141351 your result doesnt make sense?? `1 2 3 2 <-- different -> select` with `1 2 3 2 <-- don't select` i think AgRizzo has an point here.. – Raymond Nijland Dec 04 '13 at 21:28
  • Is there an PRIMARY KEY with an auto_increment?? so we use an explicit ORDER BY ... ASC instead off trusting the storage engine will always returning records in the same order... – Raymond Nijland Dec 04 '13 at 21:37
  • Please go to SQLFiddle and load up some sample data. Then show us what you want the final result to look like. – AgRizzo Dec 05 '13 at 12:39

1 Answers1

0

Something like this

SELECT * FROM TABLENAME WHERE ID NOT IN
 (SELECT t1.ID FROM tablename t1 JOIN tablename t2 ON t1.ID=t2.ID+1
 AND t1.col1=t2.col1  AND t1.col2=t2.col2 AND t1.col3=t2.col3
 AND t1.col4=t2.col4 AND t1.col5=t2.col5 AND t1.col6=t2.col6)

SQL Fiddle

Assuming auto increment ID.

Explanation:

Join the table with itself but with a row "higher",so it gets the id where the row from t1 is the same with the "next" row from t2.Then it selects all rows from the table excluding those rows from the previous operation.

Mihai
  • 26,325
  • 7
  • 66
  • 81
  • Sorry something went wrong with SQLfiddle, I changed it and you're suggestion is almost doing the job. I only need to get the lowest col6 value. – Jab Dec 05 '13 at 17:52
  • Lowest col6 value?I dont get it.Clarify. – Mihai Dec 05 '13 at 17:53
  • I think I got it now, testing it now on the my project. Sorry for my unclarity in this question. SELECT * FROM TABLENAME WHERE ID NOT IN (SELECT t1.ID FROM tablename t1 JOIN tablename t2 ON t1.ID=t2.ID+1 AND t1.col1=t2.col1 AND t1.col2=t2.col2 AND t1.col3=t2.col3 AND t1.col4=t2.col4 AND t1.col5=t2.col5) ORDER BY col6 – Jab Dec 05 '13 at 17:57