Specifice order to tables in postgres

Question

I just created a temporary table as:

create temporary table userAndProductSales as 
select p.p_name, u.u_name, u.s_price, u.quantity 
from product p 
join userAndStates u 
on p.s_id = u.s_id

Now I want to select some columns with a particular order. For example, I want the select to give me an output of:

u_name1 p_name1
u_name1 p_name2
u_name1 p_name3
u_name1 p_name4
...

u_name2 p_name1
u_name2 p_name2
u_name2 p_name3
....

and so on and so forth. How do I get this ouput? I've tried something on the lines of:

select (select u_name from userandproductsales order by u_name), p_name from userandproductsales

but I'm getting an error

UPDATE: Figured out that the table I'm joining isn't giving me the correct data I want. Thanks for the help though.

When getting an error, you should always include it. Also, it is usually good form to add what version of PostgreSQL you are using. — David S
– David S, Commented May 22, 2015 at 18:20
Also, why are you creating a temp table? Why not just order by from your query? — David S
– David S, Commented May 22, 2015 at 18:22
"order by" isn't giving me the correct output that I want. I'm making a web application so I'm using temp tables for speed reasons — Justin Case
– Justin Case, Commented May 22, 2015 at 18:26
This is a bad idea. What happens when another session adds a new product? Your "temp" table is out of date. Don't try to be smarter than PostgreSQL. Unless you have a lot of load on the database (and I'm mean 1000s of transactions per second) then just let Postgresql worry about the caching strategy for you. — David S
– David S, Commented May 22, 2015 at 18:32
This web application is quite simple and I know for sure theres no way another session will add in new products. I also have to access this same table in the same session so using temp tables will help me in my opinion — Justin Case
– Justin Case, Commented May 22, 2015 at 18:35

Houari · Accepted Answer · 2015-05-22 18:13:51Z

1

Here is how to use ORDER BY :

SELECT * from userandstatesales 
order by u_name , p_name

answered May 22, 2015 at 18:13

Houari

5,6513 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Justin Case Over a year ago

Thanks for the reply. This is close but I'm getting an output of

Justin Case Over a year ago

u_name1 p_name1, u_name1 p_name 2, ..... u_name2 p_name6, u_name2 p_name 7 and so on. I need the p_name to be consistent with u_name just like I pointed out in my question.

Houari Over a year ago

Ok, you should give a data sample storend on your tables.

David S · Accepted Answer · 2015-05-23 04:21:52Z

1

Unless there is a reason for creating a temporary table (like needing to access it later in the same session), you should avoid the expense and simply do a order by from your select. For example:

select p.p_name, u.u_name, u.s_price, u.quantity 
from product p 
join userAndStates u 
on p.s_id = u.s_id
order by u.u_name, p.p_name;

edited May 23, 2015 at 4:21

answered May 22, 2015 at 18:25

David S

14.1k13 gold badges60 silver badges97 bronze badges

2 Comments

Justin Case Over a year ago

I am actually accessing the table in the same session so I do need a temporary table. The order by isn't giving me the output I want. It orders the u_name correctly but it's not ordering p_name like I want it to

David S Over a year ago

@deryl I would probably be helpful to the community in answering your question if you will provide the DDL statements for the two tables and the inserts to populate them. Then provide the desired out put to your query.

Collectives™ on Stack Overflow

Specifice order to tables in postgres

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related