Re: order by confusion
Schalk Neethling wrote: Greetings! This might be a stupid question but here goes: I have a table that contains a column entitled current_pos. I want to search this table and then order the results by current_pos. Now I am running the following SQL query on the table: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos DESC; After running this the results are returned but as 2, 1, 0 , 0 etc. If I use: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos ASC; It returns 0, 1, 2 How do I go about getting this to return the results as 1,2,3,4 etc.? Any help would be appreciated. Thank you! Well, since the results are 0, 1, 2 you are not going to get 1,2,3,4. You are getting what you are asking for, and it appears to be what you want, so what is the problem? A bit more detail and a real example might help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: order by confusion
[snip] This might be a stupid question but here goes: I have a table that contains a column entitled current_pos. I want to search this table and then order the results by current_pos. Now I am running the following SQL query on the table: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos DESC; After running this the results are returned but as 2, 1, 0 , 0 etc. If I use: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos ASC; It returns 0, 1, 2 How do I go about getting this to return the results as 1,2,3,4 etc.? Any help would be appreciated. Thank you! [/snip] So, is 1 == 0? Or do you need to start with any non-zero position? If I use: SELECT * FROM ab_leader_board WHERE sex = 'F' AND cup = 'Kids' AND current_pos > '0' ORDER BY current_pos ASC; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
order by confusion
Greetings! This might be a stupid question but here goes: I have a table that contains a column entitled current_pos. I want to search this table and then order the results by current_pos. Now I am running the following SQL query on the table: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos DESC; After running this the results are returned but as 2, 1, 0 , 0 etc. If I use: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos ASC; It returns 0, 1, 2 How do I go about getting this to return the results as 1,2,3,4 etc.? Any help would be appreciated. Thank you! -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Landlines Tel: +27125468436 Fax: +27125468436 Web email:[EMAIL PROTECTED] Global: www.volume4.com Messenger Yahoo!: v_olume4 AOL: v0lume4 MSN: [EMAIL PROTECTED] We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- 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
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]
