If what you mean by "most recent" are the products with the latest
'post_date', try this:
SELECT ID, title, max(s.post_date) as post_date
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 ID,title
order by post_date desc
LIMIT 10
You will have an extra column of data but you should not be required to
display every column, are you?
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
|---------+---------------------------->
| | Jon Drukman |
| | <[EMAIL PROTECTED]|
| | m> |
| | Sent by: news |
| | <[EMAIL PROTECTED]|
| | rg> |
| | |
| | |
| | 07/09/2004 04:08 |
| | PM |
| | |
|---------+---------------------------->
>--------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: [EMAIL PROTECTED]
|
| cc:
|
| Fax to:
|
| 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]
