Sorting printed output

General
1

I have a query:

Select distinct on (i.item_id) i.item_id, i.title, pe.fname, pe.lname, i.base_mat,
sy.sys_value, i.orig_ts, i.price1 from sysconf sy, person pe, item i
left join memo m on ( m.owner_id = i.item_id )
where i.en_id = 1 and i.status = ‘t’ and sy.sys_id = i.avail_id
and pe.pe_id = i.pe_id and flag_as = ‘t’::boolean and flag_pl = ‘t’::boolean
order by i.item_id

I have to use “distinct on (i.item_id)” because I only want the item once, even if there are multiple (or no) memo records per item.

When I use the query to populate a listbox, it works just fine, since I can sort the listbox any which way I want.

But, life is more complicated than that: I have to print this in a report and I have to sort this by item title or by person last & first name.

I’m running the report using this code:

 If ps.PageSetupDialog Then
   dim g as graphics
   g = OpenPrinterDialog(ps, nil)
   if g <> nil then

     // if the report runs successfully
     If rpt.Run( rs, ps ) Then
       rpt.Document.Print(g)
     End If
   end if
 end if

where rs is the returned record set of the above query.
I cannot change the ‘order by’ clause, because that gives me:

SELECT DISTINCT ON expressions must match initial ORDER BY expressions at character 21

Is there any method that I can use to re-order the record set after retrieving it from the database?
If not, what can I use to build my reports differently so they show what the client (and not the system) wants?

2

use the same query on a View and then Select the view and Order BY Items or sort by

3
  1. a temporary table on the server side - insert the data from this query into it then select in whatever order you want
  2. a view you can resort
  3. bring the data locally and then select from that in whatever order

I actually wrote this & thought I’d posted it already and was surprised to find it NOT present
Odd

4 
SELECT * FROM
(
Select distinct on (i.item_id) i.item_id, i.title, pe.fname, pe.lname, i.base_mat, 
sy.sys_value, i.orig_ts, i.price1 from sysconf sy, person pe, item i 
left join memo m on ( m.owner_id = i.item_id ) 
where i.en_id = 1 and i.status = 't' and sy.sys_id = i.avail_id 
and pe.pe_id = i.pe_id and flag_as = 't'::boolean and flag_pl = 't'::boolean 
order by i.item_id
)
ORDER BY <whatever you want>
5

Thanks guys - I went with the view since that reply was posted first. Took a little re-writing of the code, but it works.
On to the next question - different thread.