0

I have a Postgresql 9.4.1 table, like this...

--------+-----------+-----------+----------------
serial  |  username |  userprod |  prodprice
--------+-----------+-----------+----------------
1       |  Zack     |  candy    |  44$
2       |  Tom      |  shoes    |  54$
3       |  Steve    |  pants    |  23$
4       |  Paul     |  hood     |  65$
5       |  John     |  cap      |  23$
6       |  Zack     |  tshirt   |  56$
7       |  Tom      |  pullover |  21$
8       |  Steve    |  socks    |  42$
9       |  Paul     |  shorts   |  23$
10      |  John     |  masc     |  21$

Rows are ordered by the serial column, how can I display the same table but, reordering all the rows based on the "username" column alphabetically descending? I would like Postgres to do it automatically on real time on a separate table. I guess I can use View? How can I do that?

litu16
  • 173
  • 5
  • 14
  • 1
    Read about `ORDER BY` clause: http://www.w3schools.com/sql/sql_orderby.asp – krokodilko Apr 30 '16 at 20:30
  • 2
    Rows in a table are ***not*** "ordered". Any order you seen when not using `order by` is pure coincidence. If you want to see them ordered, use an `order by` when selecting. That is the ***only*** way to get a guaranteed order. http://www.postgresql.org/docs/current/static/sql-select.html#SQL-ORDERBY –  Apr 30 '16 at 22:23
  • what about creating a view?? can that be done automatically?? – litu16 Apr 30 '16 at 23:57

1 Answers1

1

Sure, you can use a VIEW to return ordered rows.

CREATE VIEW v_tbl_username AS
TABLE tbl ORDER BY username;

And another one for your rows sorted by the serial column:

CREATE VIEW v_tbl_serial AS
TABLE tbl ORDER BY serial;

Read @a_horse's comment addressing your basic misconception.

The order holds unless it's overruled by another ORDER BY in the outer query.

Views work with early binding: Only columns present at the time of creation are included. SELECT * FROM tbl or TABLE tbl are resolved to the list of currently existing column. If the underlying table is changed later, that's not cascaded to the view automatically.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • thanks erwin, I knew I could use `SELECT * FROM tbl_ ORDER BY ircusername ASC, stmtserial ASC;` I just didn't know how to create it into a VIEW – litu16 May 01 '16 at 02:13