SQL - ORDER BY clause dependent on field list in SELECT

[Ruslan Zasukhin]

On 10/26/09 10:08 PM, "Steve Albin" <steve at steve-albin.com> wrote:

Hi Steve,

Best of all, please report this to mantis,
Upload simple db to reproduce this in Vstudio

Ok?

> I am using VStudio version 4.3.
> 
> I have this SQL:
> 
> SELECT DISTINCT Songs.nId, Songs.cTitle
> FROM Songs, Perfs
> WHERE Songs.cTitle  Like  'gl%'
> AND Songs.nID = Perfs.nSongId
> order by songIdx, nid
> 
> The records returned are correct, but they are sorted by nid,
> not songidx, nid.  The songidx field is ignored.
> 
> If I use this SQL then everything works as expected:
> 
> SELECT DISTINCT Songs.nId, Songs.cTitle , Songs.songIdx
> FROM Songs, Perfs
> WHERE Songs.cTitle  Like  'gl%'
> AND Songs.nID = Perfs.nSongId
> order by songIdx, nid
> 
> If I remove  DISTINCT, then the SQL sorts correctly whether or
> not I include songidx in the SELECT list.  Of course, I get the
> duplicate records which I don't want.
> 
> 
> I thought that Valentina SQL allowed us to sort a cursor on a
> field without including it in the list of fields for the
> SELECT.  Was I misinformed?  Does the DISTINCT keyword change
> the way this is supposed to work?
> 
> Steve
> 
> 
> 

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]