"TW" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> 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;
Try
SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE
classnumber=6 OR
(classnumber=7 AND hd='B') OR
classnumber=8 OR
(classnumber=9 AND hd='A')
ORDER BY random() LIMIT 2;
At the very least, use UNION ALL instead of UNION.
> 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.
Ah. Then you need something like this:
select title, ... from
(select * FROM music
WHERE (classnumber=6 OR (classnumber=7 AND hd='B'))
ORDER BY random() LIMIT 1)
UNION ALL
select title, ... from
(select * FROM music
WHERE (classnumber=8 OR (classnumber=9 AND hd='A'))
ORDER BY random() LIMIT 1);
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
