Dawson, Michael wrote:
> You could write a complex UNION query dynamically. This is what the
> result should look like:
>
> SELECT 15, '1' AS SortOrder
> FROM tbl_a
> WHERE idPrimaryKey = 15
> UNION
> SELECT 12, '2' AS sortOrder
> FROM tbl_a
> WHERE idPrimaryKey = 12
> UNION
> ....
> ORDER BY sortOrder
>
> That may get a bit ugly if you have many ids over which to loop.
>
> M!ke
>
> -----Original Message-----
> From: Dwayne Cole [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, March 09, 2005 11:23 AM
> To: CF-Talk
> Subject: OT Query Select Problem
>
> My query looks like this:
>
> =====
> select *
> from TBL_A
> where idPrimaryKey IN (15,12,1,8,4)
> =====
>
> but the results are returned in the order of
>
> 1,4,8,12,15
>
> Im using MS Access. Is there any way to have the results returned in
> the same order as the list of values passed in the IN statment?
>
> Dwayne D. Cole,MBA
> FluxFlow.com
Perhaps use an auxilliary table, SORTORDER,
PK idPrimaryKey
---- ------------
1 15
2 12
3 1
4 8
5 4
Then join to this table and order by the PK:
SELECT TBL_A.*
FROM SORTORDER
INNER JOIN TBL_A
ON SORTORDER.idPrimaryKey = TBL_A.idPrimaryKey
WHERE TBL_A.idPrimaryKey IN (15,12,1,8,4)
ORDER BY SORTORDER.PK;
This puts your sort order in the database, where it belongs.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:198016
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
