0
votes
1answer
33 views

PostgreSQL : inconsistent result when using WHERE clause on VIEW

This is my VIEW declaration CREATE OR REPLACE VIEW inv_groups_vw AS SELECT gg.id, gg.sku, gg.name, gg.description, CASE WHEN count(i.*) = 0 THEN '[]'::json ...
1
vote
2answers
64 views

In PostgreSQL is there a way to rank/weigh columns so as to order the results?

Let's say I am searching through three text columns. I don't just want results that match a given word or phrase, but I also want to weigh them (column A > column B > column C) - thereby ...
0
votes
1answer
62 views

ORDER BY that doesn't contain repeating values

Relevant SQL Fiddle (postgres 9.2) How do I state a SELECT .. ORDER BY .. that avoids having repeated values for a column? That is, for rows of the column "rank" (1,3,3,1) how do I get a result so ...
2
votes
1answer
85 views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
2
votes
2answers
85 views

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) ...
1
vote
1answer
418 views

Order by alphabet and then by numbers

I have records like A5 A4 Z1 B2 C7 C1A C11A B1 B4 I want them to be sorted in this manner A4 A5 B1 B2 B4 C1 C11A C7 Z1 using the ORDER BY clause. I want them to be sorted by alphabets ...
0
votes
1answer
283 views

Postgresql table with one integer column, sorted index, with duplicate primary key

I want to have a table in PostgreSQL with these properties: One column with an integer Allow duplicates in this column. Duplicates will be rare. Duplicates have no meaning logically and will be ...
3
votes
1answer
160 views

Bayesian ranking

I have the following table in my database: ╔════════╦═════════════╦═══════════════╗ ║ Name ║ total_stars ║ total_reviews ║ ╠════════╬═════════════╬═══════════════╣ ║ Item A ║ 27 ║ ...