Re: SELECT DISTINCT + ORDER BY confusion

2004-07-12 Thread SGreen 

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

SELECT DISTINCT + ORDER BY confusion

2004-07-09 Thread Jon Drukman 
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

2004-07-09 Thread Victor Pendleton 
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]

Re: SELECT DISTINCT + ORDER BY confusion

2004-07-09 Thread Jon Drukman 
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

2004-07-09 Thread Justin Swanhart 
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