Re: SELECT DISTINCT + ORDER BY confusion
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
RE: SELECT DISTINCT + ORDER BY confusion
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 | > +++ > (
Re: SELECT DISTINCT + ORDER BY confusion
Victor Pendleton wrote: Have you tried using a group by clause? Group by title same problem - the group by happens before the order by and you get essentially random results. -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]
RE: SELECT DISTINCT + ORDER BY confusion
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]
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]
