Selecting Last 2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mgreenway@gmail.com - 25 Feb 2006 23:30 GMT

aut_id txt_head mem_body yon_approved
1 y
2 n
3 y
4 n
5 y
6 n

Trying to select last two records added that are approved and the last
two that are not approved... what i want to return is
5
3
6
4

SELECT Top 2 * FROM tbl_page WHERE Yon_approved = True ORDER BY aut_id
DESC UNION ALL ELECT Top 2 * FROM tbl_page WHERE Yon_approved = False
ORDER BY aut_id DESC
Returns
5
4
3
2

Reply to this Message

Mark - 26 Feb 2006 00:14 GMT

I think you can do this with a _very_ ugly set of nested queries.

Try this:

SELECT [tbl_page].[aut_id]
FROM tbl_page
WHERE tbl_page.aut_id=(select max(aut_id) FROM tbl_page WHERE yon_approved =
"y") OR tbl_page.aut_id = (select max(aut_id) FROM tbl_page WHERE
yon_approved = "y" AND aut_id < (select max(aut_id) FROM tbl_page WHERE
tbl_page.yon_approved = "y"));

That should give you the "top two" approved entries.
If you get it working, try cloning it for the unapproved entries.
Your next challenge would be to splice both of them into a single query.

Good luck!
-Mark

Reply to this Message

mgreenway@gmail.com - 26 Feb 2006 00:53 GMT

(SELECT Top 2 * FROM tbl_page WHERE Yon_approved = True ORDER BY aut_id
DESC) UNION ALL (SELECT Top 2 * FROM tbl_page WHERE Yon_approved =
False
ORDER BY aut_id DESC)

Reply to this Message

Mark - 26 Feb 2006 03:47 GMT

> (SELECT Top 2 * FROM tbl_page WHERE Yon_approved = True ORDER BY aut_id
> DESC) UNION ALL (SELECT Top 2 * FROM tbl_page WHERE Yon_approved =
> False
> ORDER BY aut_id DESC)

Ok, the "top" keyword is a new one to me. Thanks.

Reply to this Message