2

I have a table that i want to filter out duplicated data when using SELECT operation, the table itself contain data like this:

|=======|
| SPEED |
|=======|
|  100  |
|  100  |
|   90  |
|   80  |
|   80  |
|  100  |
|   80  |
|=======|

What i want to is something like this, notice that the order still intact:

|=======|
| SPEED |
|=======|
|  100  |
|   90  |
|   80  |
|  100  |
|   80  |
|=======|

DISTINCT or GROUP BY didn't work since it's discard all duplicated data

Why i need this kind of data, is because i want to draw chart using this data, by reducing the node (removing duplicated data in some sequences) the chart would less crowded and faster to render

Dels
  • 2,375
  • 9
  • 39
  • 59
  • Do you have any other fields? – Craig White Apr 19 '11 at 04:31
  • Will be useful if you give complete table structure, on the basis of one column its difficult to ans you. – PHP Apr 19 '11 at 04:32
  • 1
    Tables in databases have no inherit concept of order. They are, by definition, an unordered set of rows. You must provide some other piece(s) of information A: uniquely identifies each instance and B: provides an order to the values. – Thomas Apr 19 '11 at 04:44

1 Answers1

1

Edited, since the question was clarified.

In order to archieve something like this, your table needs to have another column, lets call it Id. My test table looks like this:

CREATE TABLE `yourtable` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Speed` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM

In order to check if the following row is maybe the same value for the field speed then you might just use a LEFT JOIN and leave out all the data where both values equal.

Try this:

SELECT A.Id, A.Speed, B.* 
FROM yourtable AS A
LEFT JOIN yourtable AS B ON A.Id+1 = B.Id
WHERE A.Speed != B.Speed OR ISNULL(B.Speed);
Bjoern
  • 15,934
  • 4
  • 43
  • 48
  • yes my data was correct and the result i want was correct too...i used this for drawing chart, reducing the node was good by discarding the duplicated data – Dels Apr 19 '11 at 04:45
  • Maybe the term "duplicate" is a bit overloaded in terms of data in a database. After the clarification I've changed my original answer. Try it out! – Bjoern Apr 19 '11 at 06:16