oversight

Source Link

edited Sep 16, 2020 at 18:33

182.1k
28
457
620

I know that the order of returned rows is not guaranteed with the IN statement in Postgres. For example if I do this:

SELECT "users"users."id"id FROM "users"users WHERE "users"users."id"id IN (13589, 16674, 13588)

I may get this result:

16674
13588
13589

However, I want returned rows to respect the order in the IN list, so I found few solutions online, such as:

SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')

or

SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?

I know that the order of returned rows is not guaranteed with the IN statement in Postgres. For example if I do this:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)

I may get this result:

16674
13588
13589

However, I want returned rows to respect the order in the IN list, so I found few solutions online, such as:

SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')

or

SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?

I know that the order of returned rows is not guaranteed with the IN statement in Postgres. For example if I do this:

SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)

I may get this result:

16674
13588
13589

However, I want returned rows to respect the order in the IN list, so I found few solutions online, such as:

SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')

or

SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?

format, denoise, terminology

Source Link

edited Sep 16, 2020 at 12:31

Erwin Brandstetter

182.1k
28
457
620

Order of returned recordrows with IN statement postgres

I know that the order of returned recordsrows is not guaranteed with the IN statement for Pstgresin Postgres. For example if I do this:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)

I may get this result:

16674
13588
13589

However, I want the records returned rows to respect the order in statement inputthe IN list, so I found few solutions online, such as:

SELECT "users"users."id"id FROM "users"users WHERE "users"users."id"id IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')

or

SELECT "users"users."id"id FROM "users"users WHERE "users"users."id"id IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?

Order of returned record with IN statement postgres

I know that the order of returned records is not guaranteed with the IN statement for Pstgres. For example if I do this:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)

I may get this result:

16674
13588
13589

However, I want the records returned to respect the in statement input, so I found few solutions online, such as:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')

or

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?

Order of returned rows with IN statement

I know that the order of returned rows is not guaranteed with the IN statement in Postgres. For example if I do this:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)

I may get this result:

16674
13588
13589

However, I want returned rows to respect the order in the IN list, so I found few solutions online, such as:

SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')

or

SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?

format title

Source Link

edited Feb 24, 2014 at 0:38

Erwin Brandstetter

182.1k
28
457
620

Order of returned record with inIN statement postgres

I know that the order of returned records is not guaranteed with the IN statement for postgresPstgres. ForFor example if I do this:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)

I may get this result:

16674
13588
13589

However, I want the records returned to respect the in statement input, so I found few solutions online, such as:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')

or

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?

Order of returned record with in statement postgres

I know that the order of returned records is not guaranteed with the IN statement for postgres. For example if I do this:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)

I may get this result:

16674
13588
13589

However, I want the records returned to respect the in statement input, so I found few solutions online, such as:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')

or

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?

Order of returned record with IN statement postgres

I know that the order of returned records is not guaranteed with the IN statement for Pstgres. For example if I do this:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)

I may get this result:

16674
13588
13589

However, I want the records returned to respect the in statement input, so I found few solutions online, such as:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')

or

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?