Hi,
I have this query:
SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE classnumber=6 OR classnumber=7
AND hd="B"
UNION
SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE classnumber=8 OR classnumber=9
AND hd="A"
ORDER BY random() LIMIT 2;
And I get this error:
SQL near line 1: 1st ORDER BY term does not match any column in the
result set
The data that I need is all in the same table, but I need to run
queries based
on the classnumber. I tried a number of different ways but couldn't figure
this out, even
after googling. I figured that this was similar to the query I used in MySQL,
except I
learned that the ORDER BY clause had to be put at the end of the SELECT
statements.
I actually need to have a limit on each SELECT clause so that I only
get one
record from either classnumbers 6 or 7 and then one record from classnumbers 8
or 9, randomly
chosen. I thought that I needed to write it like this:
SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE classnumber=6 OR classnumber=7
AND hd="B"
ORDER BY random() LIMIT 1
UNION
SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE classnumber=8 OR classnumber=9
AND hd="A"
ORDER BY random() LIMIT 1;
...but that doesn't work either. Notice that I put only 1 semicolon at
the very end
of the compound statement. If I order it by one of the columns, the query
works, but only for
one SELECT statement (I can't get the UNION to work at all).
What is wrong with the way I wrote this query? Thanks.
--
VR~
TW
Email: [EMAIL PROTECTED]
"Knowledge Is Power"
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
