Google Groups Home
Help | Sign in
comp.databases.mysql
+ new post
About this group
Subscribe to this group
This is a Usenet group - learn more
group by and order by trouble
Options
There are currently too many topics in this group that display first. To make this topic appear first, remove this option from another topic.
There was an error processing your request. Please try again.
flag
   2 messages - Expand all
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
Iain Adams  
View profile
  More options Nov 23 2006, 8:27 am
Newsgroups: comp.databases.mysql
From: "Iain Adams" <aca04...@shef.ac.uk>
Date: 23 Nov 2006 05:27:53 -0800
Local: Thurs, Nov 23 2006 8:27 am
Subject: group by and order by trouble
Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author
hey,

I have a table with some columns, 2 of which are eventTime and gameId.
gameId however is not the primary key and is not unique for each
record, that is id. I want to get returned all the unique gameId's the
biggest eventTime for that gameId. Then order then by the returned
eventTime. I have tried numerous possibilities but cannot seem to get
this to work. If anybody knows please can you reply


    Reply to author    Forward  
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Captain Paralytic  
View profile
 (1 user)  More options Nov 23 2006, 9:45 am
Newsgroups: comp.databases.mysql
From: "Captain Paralytic" <paul_laut...@yahoo.com>
Date: 23 Nov 2006 06:45:31 -0800
Local: Thurs, Nov 23 2006 9:45 am
Subject: Re: group by and order by trouble
Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author

Iain Adams wrote:
> hey,

> I have a table with some columns, 2 of which are eventTime and gameId.
> gameId however is not the primary key and is not unique for each
> record, that is id. I want to get returned all the unique gameId's the
> biggest eventTime for that gameId. Then order then by the returned
> eventTime. I have tried numerous possibilities but cannot seem to get
> this to work. If anybody knows please can you reply

Something like this (what I call the Strawberry query) perhaps:

SELECT g1. *
FROM `games` g1
LEFT JOIN `games` g2 ON g1.`gameId` = g2.`gameId` AND g1.`eventTime` <
g2.`eventTime`
WHERE g2.`eventTime` IS NULL
ORDER BY g1.`eventTime`


    Reply to author    Forward  
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
End of messages
« Back to Discussions « Newer topic     Older topic »

Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2008 Google