Group über zwei Felder und Sortierung

[Forumsgast]

Hallo,

heute habe ich mal wieder ein kleines Problemchen...

Ich habe eine Tabelle mit folgendem Aufbau:

CREATE TABLE `Positionen` (
  `Id` int(11) NOT NULL auto_increment,
  `GpsProviderId` int(11) NOT NULL,
  `Mobilnummer` varchar(25) collate utf8_unicode_ci NOT NULL,
  `NaviId` varchar(10) collate utf8_unicode_ci NOT NULL,
  `Timestamp` timestamp NULL default '0000-00-00 00:00:00',
  `Longitude` decimal(10,6) NOT NULL,
  `Latitude` decimal(10,6) NOT NULL,
  `Hoehe` decimal(10,1) NOT NULL,
  `Geschwindigkeit` decimal(10,1) NOT NULL,
  `Richtung` int(11) NOT NULL default '0',
  `Sateliten` int(11) NOT NULL default '0',
  PRIMARY KEY  (`Id`),
  KEY `NaviId` (`NaviId`),
  KEY `Mobilnummer` (`Mobilnummer`)
) ENGINE=InnoDB;

In dieser Tabelle liegen Positionsdaten von verschiedenen Navis.
Ein Navi wird entweder über die Mobilnummer oder über die NaviId referenziert.
D.h. entweder ist die Mobilnummer gesetzt ODER die NaviId.

Nun möchte ich möglichst Ressourcen schonend die jeweils aktuellsten Daten aller Navis abfragen.

Versucht habe ich es über:

SELECT Positionen.*
FROM Positionen
WHERE Timestamp=(
    SELECT MAX(Posits.Timestamp)
    FROM Positionen as Posits
    WHERE Positionen.NaviId=Posits.NaviId OR Positionen.Mobilnummer=Posits.Mobilnummer
)

Aber da liefert er mir nicht alle Geräte.
Kann mir vielleicht mal jemand einen Stupser in die richtige Richtung geben?

[Forumsgast]

Mein erster Gedanke wäre, die Fälle „ID gesetzt“ und „Nummer gesetzt“ einzeln abzuarbeiten und mit UNION zu verbinden.

Innerhalb der beiden zu vereinigenden Queries würde ich dann mit einem within-group aggregate versuchen, die jeweils aktuellste Zeile eines Eintrags zu erhalten.

http://www.artfulsoftware.com/infotree/queries.php#101

Bei Verzicht auf UNION ließe sich vielleicht mit einer IF-Funktion was basteln.

[Forumsgast]

Kannst du das Schema ändern? Du schleppst die Problematik, welcher Wert nun gesetzt ist, sonst durch das gesamte System. Ich würde einen internen Schlüssel verwenden oder beide kombinieren, durch (ggf. beidseitigen) Zerofill.

navi
id (PK AI) | navi_unqiue (UN, char)
1 | 827465287452 00000000000000
2 | 000000000000 00000000089483
(evtl. musst du ihn nicht mal kombinieren, sondern kannst beide Spalten gemeinsam auf UNIQUE setzen; vorher aber nochmal NULL-Problematik gegenprüfen, also ob NULL in einer kombinierten UNIQUE-Spalte Probleme bereitet)

position
id (PK AI) | ..

[Forumsgast]

Habs jetzt anders "vereinfacht". Habe das Feld NaviId gestrichen da die Navis die eine ID besitzen ebenfalls eine Mobilnummer haben.
Die lasse ich jetzt mit eintragen.
Nun muß ich nur noch ein Feld gruppieren und dort die jeweils aktuellsten Werte ausgeben lassen.

SELECT *
FROM (
    SELECT *
    FROM Positionen
    ORDER BY Timestamp DESC
) as s
GROUP BY Mobilnummer

So gibt er es richtig aus.
Habt ihr vielleicht noch was was weniger Datenbanklastig ist? Für jedes Navi wird alle fünf Sekunden ein neuer Wert eingetragen, entsprechend groß ist natürlich auch die Tabelle

[Forumsgast]

Ich weiß es nicht sicher, aber ich würde nicht unbedingt drauf wetten, dass deine Query definiert den neuesten Timestamp pro Gruppe zurückliefert. Siehe mein Link für Alternativen.

Was Optimierungen angeht: Eine Option wäre es, eine Extratabelle anzulegen, die nur den aktuellsten Eintrag (oder einen Fremdschlüssel darauf) pro Gerät hält. Aber ob das schlau ist, kann ich nicht wirklich beurteilen.

[Forumsgast]

Doch, gibt den neusten aus, die Tabelle ist ja bereits mit Werten gefüllt

[Forumsgast]

Sehe gerade, dass die Lösung sogar auf der Seite aufgeführt wird, die ich verlinkt habe (im selben Abschnitt ziemlich gegen Ende). Hm…

In der MySQL-Doku heißt es:

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

- http://dev.mysql.com/doc/refman/5.1/...n-columns.html

*schulterzuck*