CODEWITHSUNDEEP
mysql
Lee Armstrong
Lee Armstrong

Reputation: 11450

MySQL Query eliminate duplicates but only adjacent to each other

I have the following query..

    SELECT Flights.flightno, 
    Flights.timestamp, 
    Flights.route
FROM Flights
WHERE Flights.adshex = '400662' 
ORDER BY Flights.timestamp DESC

Which returns the following screenshot. results

However I cannot use a simple group by as for example BCS6515 will appear a lot later in the list and I only want to "condense" the rows that are the same next to each other in this list.

An example of the output (note BCS6515 twice in this list as they were not adjacent in the first query) enter image description here

Which is why a GROUP BY flightno will not work.

Upvotes: 3

Views: 264

Answers (2)

jswolf19
jswolf19

Reputation: 2303

I don't think there's a good way to do so in SQL without a column to help you. At best, I'm thinking it would require a subquery that would be ugly and inefficient. You have two options that would probably end up with better performance.

One would be to code the logic yourself to prune the results. (Added:) This can be done with a procedure clause of a select statement, if you want to handle it on the database server side.

Another would be to either use other information in the table or add new information to the table for this purpose. Do you currently have something in your table that is a different value for each instance of a number of BCS6515 rows?

If not, and if I'm making correct assumptions about the data in your table, there will be only one flight with the same number per day, though the flight number is reused to denote a flight with the same start/end and times on other days. (e.g. the 10a.m. from NRT to DTW is the same flight number every day). If the timestamps were always the same day, then you could use DAY(timestamp) in the GROUP BY. However, that doesn't allow for overnight flights. Thus, you'll probably need something such as a departure date to group by to identify all the rows as belonging to the same physical flight.

Upvotes: 2

Scherbius.com
Scherbius.com

Reputation: 3414

GROUP BY does not work because 'timestamp' value is different for 2 BCS6515 records.

it will work only if:

SELECT Flights.flightno, 
    Flights.route
FROM Flights
WHERE Flights.adshex = '400662' 
GROUP BY (Flights.flightno)

Upvotes: 0

Related Questions