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!

  • 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

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.
 
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
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top