--- In firebird-support@yahoogroups.com, "bwc3068" wrote: > > hi-- > > always a great place to get help!! > > here's what i have data wise: > > Tag ID Date > > ABC 11 2012 > DEF 11 2011 > GHJ 11 2010 > HHH 22 2012 > ZZZ 22 2011 > AAA 22 2010 > AAK 33 2012 > AAD 44 2012 > YYY 44 2010 > > IF i select * order by tag, date > i get > > AAA 22 > AAD 44 > AAK 33 > ABC 11 > DEF 11 > GHJ 11 > HHH 22 > YYY 44 > ZZZ 22 > > My desire is to still keep the "groups" of ID together > > i'd LIKE > > AAA 22 > HHH 22 > ZZZ 22 > AAD 44 > YYY 44 > AAK 33 > ABC 11 > DEF 11 > GHJ 11 > > but > select * order by tag, date group by ID > doesn't work > > NOTE i do NOT want to sort by ID. I just want the list sorted by Tag, > subsorted by date. BUT I want all the IDs together because the Tag might > change from date to date for ID > > any thoughts or hints? > thanks > kelly >
Hi, as you can see you describe something what is not grouping and not sorting at all > AAA 22 > HHH 22 > ZZZ 22 > AAD 44 > YYY 44 > AAK 33 > ABC 11 > DEF 11 > GHJ 11 try to explain self what you need 1. You need get first tag ordered asc 2. next you need get all tags with same id ordered asc by tag 3. next you need next tag with id different then all previusly taken id look at point 3 this is not possible with sql also i think with recursive CTE but may be i missing something but this can be simply accomplished by execute block with knowing count of records and self join - may by is something simpler SET TERM ^ ; EXECUTE BLOCK RETURNS(ID INTEGER, TAG VARCHAR(40), ROK INTEGER) AS DECLARE VARIABLE ILE INTEGER; BEGIN SELECT COUNT(*) FROM XXX INTO :ILE; FOR select X2.TAG, X2.ID, X2.ROK from (SELECT * FROM XXX A1 ORDER BY A1.TAG) AS X1 LEFT JOIN (SELECT * FROM XXX A2 ORDER BY A2.TAG) X2 ON X2.ID=X1.ID ROWS :ILE INTO :TAG, :ID, :ROK DO suspend; END^ SET TERM ; ^ regards, Karol Bieniaszewski
