Help with sorting (ie. ORDER BY expression)

-----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-----

-----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.

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