Aus mehreren Kategorien das jeweils aktuellste Item holen

[nixgut]

Hi Leute,

ich möchte aus mehreren Kategorieren das jeweils aktuellste Item.

Ich habe schon alles Mögliche an GROUP BY, HAVING, max(..) Varianten versucht. Kein Erfolg.

Wer kann mir sagen, wie man innerhalb einer GROUP-Anweisung nochmals sortiert - sozusagen eine interne Sortierung vor dem ORDER BY.

Oder falls man GROUP BY weglassen könnte, wie könnte man ein LIMIT 1 auf die Kategorie anwenden?



Vielen Dank im voraus.

Ein Anfänger



Wir haben eine Tabelle mit den Feldern
id, cat, item, time

Und diesen Daten
1 | 1 | A | 2
--------------
2 | 1 | B | 9
--------------
3 | 1 | C | 7
--------------
4 | 2 | D | 8
--------------
5 | 2 | E | 10
--------------
6 | 3 | F | 3

SELECT id, cat, item, time FROM demo ORDER BY time DESC
Liefert wie zu erwarten alle Items, sortiert nach Time absteigend

5 | 2 | E | 10
--------------
2 | 1 | B | 9
--------------
4 | 2 | D | 8
--------------
3 | 1 | C | 7
--------------
6 | 3 | F | 3
--------------
1 | 1 | A | 2

SELECT id, cat, item, time FROM demo GROUP BY cat ORDER BY time DESC
Liefert wie gewünscht genau ein Item aus einer Kategorie, jedoch das Falsche, was die Time anbelangt.
Es scheint, als sortiere hier mySQl nach der ID (Primery) aufsteigend.

4 | 2 | D | 8
-------------
6 | 3 | F | 3
-------------
1 | 1 | A | 2

Gewünscht ist jedoch das folgende Ergebnis:

5 | 2 | E | 10
--------------
2 | 1 | B | 9
--------------
6 | 3 | F | 3



Im Anschluss die Demo-Datenbank und der php-Democode

Gegeben sei beispielsweise dieser Datenbestand:

CREATE TABLE demo (
  id int(6) unsigned NOT NULL auto_increment,
  cat tinyint(6) unsigned NOT NULL default '0',
  item varchar(50) NOT NULL default '',
  time tinyint(6) unsigned NOT NULL default '0',
  PRIMARY KEY (Id)
) ENGINE=MyISAM;

INSERT INTO demo VALUES("1", "1", "A in 1", "2");
INSERT INTO demo VALUES("2", "1", "B in 1", "9");
INSERT INTO demo VALUES("3", "1", "C in 1", "7");
INSERT INTO demo VALUES("4", "2", "D in 2", "8");
INSERT INTO demo VALUES("5", "2", "E in 2", "10");
INSERT INTO demo VALUES("6", "3", "F in 3", "3");

Hier ein Beispielscript

<?php

$db = mysql_connect('localhost', 'root', '');
mysql_select_db('demo', $db);

$bgcol[1] = '#ffaaaa';
$bgcol[2] = '#aaffaa';
$bgcol[3] = '#aaaaff';

echo "<h3>Alle Items, aufgelistet nach Time absteigend</h3>";
$result = mysql_query("SELECT id, cat, item, time FROM demo ORDER BY time DESC");

while($row = mysql_fetch_assoc($result))
{
	echo '<p style="background-color:'.$bgcol[$row['cat']].'">CAT-'.$row['cat'].' ..... ID-'.$row['id'].' ..... '.$row['item'].' ..... '.$row['time'].'</p>';
}

echo "<h3>Alle Items, aufgelistet nach Time absteigend, mit GROUP BY cat</h3>";
$result = mysql_query("SELECT id, cat, item, time FROM demo GROUP BY cat ORDER BY time DESC");

while($row = mysql_fetch_assoc($result))
{
	echo '<p style="background-color:'.$bgcol[$row['cat']].'">CAT-'.$row['cat'].' ..... ID-'.$row['id'].' ..... '.$row['item'].' ..... '.$row['time'].'</p>';
}

echo "<h3>Das gewünschte Ergebnis sollte aber so aussehen</h3>";
echo '<p style="background-color:'.$bgcol[2].'">CAT-2 ..... ID-5 ..... E in 2 ..... 10</p>';
echo '<p style="background-color:'.$bgcol[1].'">CAT-1 ..... ID-2 ..... B in 1 ..... 9</p>';
echo '<p style="background-color:'.$bgcol[3].'">CAT-3 ..... ID-6 ..... F in 3 ..... 3</p>';

?>

[derHund]

du mußt die tabelle mit sich selbst joinen, so, daß für nicht der bedingung entsprechende datensätze NULL verwendet wird (LEFT/RIGHT). als bedingung nimmst du den zeitvergleich. du erhälts für jede außer der spätesten zeit einen entsprechenden eintrag, bei der spätesten zeit erhälts du NULL (es ist ja kein späterer zeitpunkt vorhanden). danach suchst du im WHERE. da du das ergebnis in gruppen aufteilen willst, mußt du noch nach gleichheit der cat beim JOIN schauen.

SELECT d1.*
FROM demo d1
LEFT JOIN demo d2
ON d2.time > d1.time AND d1.cat = d2.cat
WHERE d2.id IS NULL

[nixgut]

Vielen Dank, das hat gefunzt.
Interessanter Lösungsansatz.


Da kommt mir die Frage auf:
Wie könnte man X Items pro Kategorie ausgeben, also z.b die 3 aktuellsten?


Vielen Dank noch mal, Du hast mir echt geholfen.

[nixgut]

sorry für den Doppelpost, habe mich verklickt.


Bitte den unteren lesen.

[nixgut]

Nun sitze ich wieder seit 2 Tagen an einem Problem.

Ich möchte die obige Abfrage um eine weitere Bedingung erweitern:


Wir haben eine Tabelle mit den Feldern

id, cat, item, time, cond
1 | 1 | A | 2 | 0
-------------------
2 | 1 | B | 9 | 0
-------------------
3 | 1 | C | 7 | 1
-------------------
4 | 2 | D | 8 | 1
-------------------
5 | 2 | E | 10 | 0
-------------------
6 | 3 | F | 3 | 0


Das gewünschte Ergebnis wäre die aktuellsten Elemente mit cond=1 aus den Kategorien, also 2 Elemente aus 3 Kategorien.

id, cat, item, time, cond
4 | 2 | D | 8 | 1
3 | 1 | C | 7 | 1


Die aktuelle SQL-Abfrage sieht so aus:

$result = mysql_query("    
            SELECT 
                a.id, a.cat, a.item, a.time 
            FROM 
                demo a
            LEFT JOIN
                demo b ON b.cat=a.cat AND b.time>a.time
            WHERE
                b.id is null
            ORDER BY
                a.time DESC
            "); 

Bzw. auch mit einem GROUP BY a.cat


Wo setze ich ein AND cond=1 ein, damit ich wie oben aufgeführt die zwei korrekten Ergebnisse erhalte.

Ich hatte es bereits im LEFT JOIN Teil, was jedoch nicht funzte, da trotzdem 3 Ergebnisse (falsche) geliefert wurden.
Im WHERE-Teil führte es ebenfalls nicht zum gewünschten Ergebnis.
Und im HAVING führte es zu einer Fehlermeldung (GROUP BY war enthalten)


Eine Frage noch, ist es normal, dass eine Abfrage mit LEFT JOIN dennoch Ergebnisse liefert, auch wenn die Bedingungen nicht erfüllt sind?

Und wäre MySQL 4.1 flexibler und funktionierender als diese Methode?

Danke im voraus.