Re: ... reporting a bug... asking an SQL question (joins, order by,and group by)

[Tobias Downer <toby@xxxxxxxxx>]

Hi,

Strictly speaking Mckoi should generate an error for the query you tried 
but it doesn't because this is the same way a lot of other SQL database 
systems work.  For your query;

   select *
   from ranked_genre
   group by artist
   order by artist asc, rank asc, created desc;

The 'rank', 'genre' and 'created' data selected from the table is not 
done in a deterministic way.  Adding 'rank' and 'created' to the order 
by clause will not change this.

The final query you came up with seems like a reasonable solution to 
your problem.  I've been trying to think of a simpler solution but I'm 
unable to.

Toby.


Tom Aviv wrote:

> Mckoi folks,
> 
> I think I'm reporting a bug, and I'm certainly asking an SQL question.
> 
> I've reduced the broken query down as much as I have, but still have 
> some SQL to work though to demonstrate my question and what I think is a 
> bug.
> 
> I'm using McKoi very happily and developing a trust network related 
> application.  The application uses information from many users about the 
> same topic and, among other things, shows any user a view of the data 
> according to whom he/she trusts.
> 
> - The users are stating what genre different artists should be
> categorized (as in the I say Beatles are Pop, he says Rock, she says
> Oldies, etc...)
> 
> - Usernames have been replaced by "rank" (higher rank is stronger.)
> 
> For certain views of the data, I just want to show the "best" single 
> answer.  This is the most recent thing that the highest ranking person 
> said.  Discussion continues after entering data.