Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Different distinct and order expressions

Status
Not open for further replies.
feherke

feherke

Programmer
Aug 5, 2002
9,540
RO
Hi

I need to display a list of subtotals for the records which have the same number field, but in the original order, provided by the id field. The following generates error, because the distinct and the order by expressions are not the same.

SELECT DISTINCT ON (number) number, sum(value) FROM report ORDER BY id;

Have anyone an idea, how to do this ?

Thanks,
Feherke.
 
Sort by date Sort by votes
Hi feherke,

You might try using a multi-column index. If your table is large, using a multi-column index should not only yeild the result you want, but should also improve the speed of execution.


LelandJ Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox
 
Upvote 0 Downvote
Hi

Thanks, but indexes does not resolve this. The resulted order is altered like without indexes.

Finally I solved this temporarily with filling a secondary table with the subtotals and the number field. Now I make the select from the secondary inner joined with the first.

Stupid solution, but is my best :( .

Feherke.
 
Upvote 0 Downvote
Hi feherke,

Sorry I couldn't be of more help. I havn't worked extensively with selects, but I do know postgres support sub_selects. Anyway, you have something working right now, and as you learn more about postgres, you can improve upon it as you learn.

LelandJ
Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dbuxton101
  • Locked
  • Question
Simple way of Exporting and Importing a Table
Replies
0
Views
119
dbuxton101
dbuxton101
rs120p
  • Locked
  • Question
Which way to move pg_xlog why sym link and not Linux Mount point?
Replies
0
Views
115
rs120p
rs120p
TrojanWarBlade
  • Locked
  • Question
pgpool and autovacuum cause dropped pgpool connections
Replies
0
Views
62
TrojanWarBlade
TrojanWarBlade
gacaccia
  • Locked
  • Question
PSQL and -R (record separator)
Replies
1
Views
98
feherke
feherke
abenstex
  • Locked
  • Question
SELECT DISTINCT and ORDER BY
Replies
4
Views
81
abenstex
abenstex

Part and Inventory Search

Sponsor

Back
Top