If you are usign 4.1 you could try: SELECT DISTINCT d, title FROM (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
otherwise: 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 group by p.id, p.title order by p.title limit 10 --- Victor Pendleton <[EMAIL PROTECTED]> wrote: > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
