SQL - ORDER BY clause dependent on field list in SELECT

[Steve Albin]

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




-- 
Steve Albin - Montclair, NJ
http://www.jazzdiscography.com/
http://www.steve-albin.com/