Skip site navigation (1) Skip section navigation (2)

Re: Reordering results for a report

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Nathaniel Price <nprice(at)tesseract(dot)net>
Cc: <pgsql-php(at)postgresql(dot)org>
Subject: Re: Reordering results for a report
Date: 2003-12-12 23:09:53
Message-ID: Pine.LNX.4.33.0312121606150.19061-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-php
Oh, in that case you're gonna need to use a case switch type setup and 
order by that.

select a,
  case when a=3 then 0
       when a=1 then 1
       when a=2 then 2
       else 99999 
 end as ob
from table
order by ob;

kinda thing.

On Fri, 12 Dec 2003, Nathaniel Price wrote:

> Sorry, that's not really what I'm looking for. When I said that the sort 
> order could be arbitrary, I meant /arbitrary/. As in "no amount of ORDER 
> BYs will save me from this one" arbitrary; the records could be in any 
> order the user specifies.
> 
> The idea is that in PHP I'd be using an array to keep track of the 
> custom order that the results should be returned in, using the array 
> index to store the sorting order and the values to store the table's 
> primary key, like so (note that this isn't the code I'm using, it's just 
> an example):
> 
> <?php
> $sort[0] = 3; //First record to return (3 is the primary key of the record)
> $sort[1] = 1; //Second record to return
> $sort[2] = 2; //Third record to return
> ... //and so on.
> ?>
> 
> What I'm hoping is that somehow I can use that array to make a query 
> that will return the records in the order that is specified without 
> having to create an extra table in the database just to store the sort 
> order that I want to use and joining on it. However, as I mentioned in 
> my reply to Bruno Wolff III, I'll probably just use temporary tables to 
> do it, unless anyone can show me a more elegant solution...
> 
> Thanks anyway for your reply.
> 
> On 12/12/2003 12:38 PM, scott.marlowe wrote :
> 
> >First, I'm crossposting this to pgsql-php, please remove the pgsql-general 
> >header when next someone responds.
> >
> >OK, here's how ya do it.  something like this:
> >
> >First, after you run a select, you can use pg_field_name to iterate over 
> >the list of fields you're getting back.  I.e. if your select was something 
> >like:
> >
> >select a1/a2 as div, a1+a2 as sum, a1-a2 as diff, a1, a2 from table;
> >
> >you could use this:
> >
> >$count = pg_num_fields($res);
> >if (isset($flds)) unset($flds);
> >for ($i=0;$i<$count;$i++){
> >  $flds[]=pg_field_name($res,$i);
> >}
> >
> >Now, when you're printing out the headers for each row, just make the link 
> >have something like:
> >
> >print "<url goes here...>?orderby=".$flds[$i]."moreurlstuffhere???";
> >
> >Then, if the orderby is set when you build your query, just append it:
> >
> >if (isset($orderby)){
> >  $query.= "order by ".$orderby"
> >}
> >
> >Add some directional control:
> >
> >if (isset($dir)){
> >  if ($dir=="down") $query.=" DESC";
> >}
> >
> >There's more you can do, but does that kinda get the idea across?  sorry 
> >if it's not real detailed.
> >
> >On Fri, 12 Dec 2003, Nathaniel Price wrote:
> >
> >  
> >
> >>I'm new to this list, so I'm not sure if this is the right place to post 
> >>this. If not, please direct me to where it would be better to post it.
> >>
> >>Anyway, I'm creating a report generation tool of sorts in PHP for a 
> >>database. As part of this report generation tool, I'd like to allow the 
> >>user to reorder these results arbitrarily. In other words:
> >>
> >>id | offer
> >>---+------------
> >>1  | Offer 1
> >>2  | Offer 2
> >>3  | Offer 3
> >>
> >>could become
> >>
> >>id | offer
> >>---+------------
> >>3  | Offer 3
> >>1  | Offer 1
> >>2  | Offer 2
> >>
> >>However, I don't see any way of reordering the results arbitrarily, 
> >>short of creating a table that maps the id numbers to an arbitrary sort 
> >>order, joining them and doing an ORDER BY on the sort order numbers, 
> >>like so:
> >>
> >>id | offer     | sort
> >>---+-----------+------
> >>3  | Offer 3   | 1
> >>1  | Offer 1   | 2
> >>2  | Offer 2   | 3
> >>
> >>The problems that I have with this solution are
> >>--The sort order would be unique for anybody who uses the system, in 
> >>other words, one user may sort one way, and another user another way, 
> >>and perhaps simultaneously. I could fix this by using an additional 
> >>session identifier in the sort table, but that leads me to the next 
> >>problem...
> >>--I'd have to garbage collect this data everytime I'm finished with it, 
> >>and since it's likely to only be used once for the actual report 
> >>generation and then discarded, it seems like a waste of effort.
> >>
> >>So is there a way to make a query where I can sort arbitrarily without 
> >>having to create additional data in the database?
> >>
> >>Thanks for your attention.
> >>
> >>
> >>    
> >>
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 9: the planner will ignore your desire to choose an index scan if your
> >      joining column's datatypes do not match
> >
> >
> >  
> >
> 
> 
> 


In response to

Responses

pgsql-php by date

Next:From: Bruno Wolff IIIDate: 2003-12-13 23:47:32
Subject: Re: Reordering results for a report
Previous:From: Nathaniel PriceDate: 2003-12-12 22:40:34
Subject: Re: Reordering results for a report

pgsql-general by date

Next:From: eleinDate: 2003-12-13 03:18:06
Subject: Requesting Articles for PostgreSQL General Bits
Previous:From: Rick GiggerDate: 2003-12-12 23:09:49
Subject: Re: add column sillyness

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group