Have you tried using a group by clause? Group by title -----Original Message----- From: news To: [EMAIL PROTECTED] Sent: 7/9/04 3:08 PM Subject: SELECT DISTINCT + ORDER BY confusion
I've got a product & story setup where there can be multiple stories of a given type for any product. I want to find the names of the products with the most-recently-posted stories of a certain type. This query works well: SELECT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 +--------+----------------------------------------------------+ | id | title | +--------+----------------------------------------------------+ | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989 | The Lord of the Rings, The Battle for Middle-earth | | 914811 | The Sims 2 | | 919973 | RollerCoaster Tycoon 3 | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | +--------+----------------------------------------------------+ however since there are multiple stories of the correct type for some of those products, i would like to dedupe the results and just get a unique list of products. however, if i use SELECT DISTINCT it applies that BEFORE it does the sort, so i don't get only the most recent products. what i actually get seems to be pretty random. SELECT DISTINCT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 +--------+---------------------------------------+ | id | title | +--------+---------------------------------------+ | 917958 | Port Royale 2 | | 920457 | Cuban Missile Crisis | | 915000 | Axis & Allies | | 919602 | Blitzkrieg: Burning Horizon | | 914594 | SuperPower 2 | | 914911 | Kohan II: Kings of War | | 915017 | Sid Meier's Pirates! | | 918842 | Warlords Battlecry III | | 919973 | RollerCoaster Tycoon 3 | | 920314 | Immortal Cities: Children of the Nile | +--------+---------------------------------------+ that's pretty messed up. really what i'd like is: +--------+----------------------------------------------------+ | id | title | +--------+----------------------------------------------------+ | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989 | The Lord of the Rings, The Battle for Middle-earth | | 914811 | The Sims 2 | | 919973 | RollerCoaster Tycoon 3 | | 915040 | Soldiers: Heroes of World War II | | 914468 | Perimeter | | 915000 | Axis & Allies | | 914811 | The Sims 2 | | 918989 | The Lord of the Rings, The Battle for Middle-earth | +--------+----------------------------------------------------+ (i built this by hand just to demonstrate the desired outcome.) is there any way to do a post-order distinct? -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
