Обсуждение: Help with sorting (ie. ORDER BY expression)
Hello, I am running postgres-7.3. I have a query like this: SELECT question_id, question_text FROM quiz_table WHERE question_id IN (2,10,3,6,4,5); But I want the output to be sorted in the way I give the question_id, something like: SELECT question_id, question_text FROM quiz_table WHERE question_id IN (2,10,3,6,4,5) ORDER BY question_id (2,10,3,6,4,5) Is there any way I can do that, so that the output of the query is question_id, text 2 ... 10 3 6 4 5 Thanks for any help. RDB -- Reuben D. Budiardja Dept. Physics and Astronomy University of Tennesse, Knoxville, TN
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, This can be easily done with pl/pgsql, visit the documentation at http://www.postgresql.org/docs/7.3/interactive/programmer-pl.html OT: seems like this is a questionnaire/survey application, yes? - ----- Jonel Rienton http://blogs.road14.com Software Developer, *nix Advocate On Feb 5, 2005, at 4:25 PM, Reuben D. Budiardja wrote: > > Hello, > I am running postgres-7.3. I have a query like this: > > SELECT question_id, question_text > FROM quiz_table > WHERE question_id IN (2,10,3,6,4,5); > > But I want the output to be sorted in the way I give the question_id, > something like: > > SELECT question_id, question_text > FROM quiz_table > WHERE question_id IN (2,10,3,6,4,5) > ORDER BY question_id (2,10,3,6,4,5) > > Is there any way I can do that, so that the output of the query is > > question_id, text > 2 ... > 10 > 3 > 6 > 4 > 5 > > Thanks for any help. > > RDB > > -- > Reuben D. Budiardja > Dept. Physics and Astronomy > University of Tennesse, Knoxville, TN > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -----BEGIN PGP SIGNATURE----- Version: PGP 8.1 iQA/AwUBQgVK+eAwOVAnbsGCEQJffgCglLCH0/MuBvAWNyIu2UHPJpvwDnYAoLkm yAPhmAdZf2Pmm330GJvx9Sci =l+eD -----END PGP SIGNATURE-----
On Sat, Feb 05, 2005 at 17:25:06 -0500, "Reuben D. Budiardja" <techlist@voyager.phys.utk.edu> wrote: > > Hello, > I am running postgres-7.3. I have a query like this: > > SELECT question_id, question_text > FROM quiz_table > WHERE question_id IN (2,10,3,6,4,5); > > But I want the output to be sorted in the way I give the question_id, > something like: > > SELECT question_id, question_text > FROM quiz_table > WHERE question_id IN (2,10,3,6,4,5) > ORDER BY question_id (2,10,3,6,4,5) > > Is there any way I can do that, so that the output of the query is You could create a table with the question ids and their rank and join that table to the above query and order by the rank.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, This can be easily done with pl/pgsql, visit the documentation at http://www.postgresql.org/docs/7.3/interactive/programmer-pl.html OT: seems like this is a questionnaire/survey application, yes? - ----- Jonel Rienton http://blogs.road14.com Software Developer, *nix Advocate On Feb 5, 2005, at 4:25 PM, Reuben D. Budiardja wrote: > > Hello, > I am running postgres-7.3. I have a query like this: > > SELECT question_id, question_text > FROM quiz_table > WHERE question_id IN (2,10,3,6,4,5); > > But I want the output to be sorted in the way I give the question_id, > something like: > > SELECT question_id, question_text > FROM quiz_table > WHERE question_id IN (2,10,3,6,4,5) > ORDER BY question_id (2,10,3,6,4,5) > > Is there any way I can do that, so that the output of the query is > > question_id, text > 2 ... > 10 > 3 > 6 > 4 > 5 > > Thanks for any help. > > RDB > > -- > Reuben D. Budiardja > Dept. Physics and Astronomy > University of Tennesse, Knoxville, TN > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -----BEGIN PGP SIGNATURE----- Version: PGP 8.1 iQA/AwUBQgVKluAwOVAnbsGCEQIc4ACfXgnHExJ7MnYzp419Sq1g7mCkErMAniW3 xqKCBaSCrpX2JDm5cHnpnOy4 =52vz -----END PGP SIGNATURE-----
> This can be easily done with pl/pgsql, visit the documentation at > http://www.postgresql.org/docs/7.3/interactive/programmer-pl.html > OT: seems like this is a questionnaire/survey application, yes? > - ----- > Jonel Rienton FWIW, given the signature: "Reuben D. Budiardja, Dept. Physics and Astronomy" he's probably trying to utilize a data base to build an inventory of test questions for the students he is responsible for actually teaching. >> I am running postgres-7.3. I have a query like this: >> SELECT question_id, question_text >> FROM quiz_table >> WHERE question_id IN (2,10,3,6,4,5); >> But I want the output to be sorted in the way I give the question_id, >> something like: >> SELECT question_id, question_text >> FROM quiz_table >> WHERE question_id IN (2,10,3,6,4,5) >> ORDER BY question_id (2,10,3,6,4,5) >> Is there any way I can do that, so that the output of the query is >> question_id, text >> 2 ... >> 10 >> 3 >> 6 >> 4 >> 5 Your understanding of the ORDER BY clause is off. My approach would be to add a column "quiz_item_list_order", type integer, and explicity specify the rank order in which you want questions to be returned. SELECT quiz_item_list_order, question_id, question_text FROM quiz_table WHERE question_id IN (2,10,3,6,4,5) ORDER BY quiz_item_list_order; -- BMT