group by order by

[#1]

1) what's wrong with this statement. I keep getting expression error.

table:

LOAD ActNo,

count(ActNo) as Uniq,  

firstname,

lastname,

expyear,

expmonth,

expday

from a qvd       

GROUP BY ActNo order by ActNo, expmonth, expyear desc;

2) if I am just looking into grouping by something without actually doing any aggregation how can I perform group by and then order by.

[maxgro]

use peek

SET DateFormat='DD-MM-YYYY';

tmp:

load * inline [

AcctNo ,    Orderdate      ,   Expdate          ,     item

123    ,       18-02-2014  ,     21-02-2015     ,       5

128    ,        4-5-2014   ,         6-6-2015   ,           32

125    ,        20-02-2014 ,      4-4-2015      ,           7

123    ,        21-03-2014 ,    21-02-2015      ,       5

125    ,        18-05-2014 ,      4-4-2015      ,           7

128    ,        2-2-2014   ,        5-3-2015    ,           21

];

final:

NoConcatenate load

*

Resident tmp

where Peek(AcctNo) <> AcctNo

order by AcctNo, Orderdate;          // here Orderdate or Orderdate desc depending on requirement

drop table tmp;

[robert99]

why not this entry

123

21-03-2014

21-02-2015

5

And what if one entry has the latest order date and another the latest expiry date. Which one would you select

[#4]

Hi RJ

that is not possible. 2 records could have latest expiry but 2 order date. not 1 each.

[#5]

Hi Massimo

It works, thanks. Can you please explain the logic.

where Peek(AcctNo) <> AcctNo

order by AcctNo, Orderdate;          // here Orderdate or Orderdate desc depending on requirement

drop table tmp;

[maxgro]

peek( 'AcctNo' )

returns the value of AcctNo in the previous record read

where peek(AcctNo) <> AcctNo --> you only keep the record when AcctNo is different from the previous AcctNo

if the table is ordered by AcctNo and Orderdate

1  10-10-2013     this one

1   12-10-2013

1   14-10-2013

2   20-1-2012     this one

2   10-2-2014

if you order by date desc

2   10-2-2014   this one

2   20-1-2012  

1   14-10-2013 this one

1   12-10-2013 

1  10-10-2013    

[robert99]

maybe one approach would be to load and then do somnething like this

load

AcctNo,   

max (Orderdate) as ,

firstsortedvalue (distinct ExpDate, - OrderDate as     ,

firstsortedvalue (distinct item, - OrderDate as

resident xxxx group by AccNo;

But this would always pick up the latest order date

OR

DateFormat='DD-MM-YYYY';

tmp:

load * inline [
AcctNo ,    Orderdate      ,   Expdate          ,     item

123    ,       18-02-2014  ,     21-02-2015     ,       5
128    ,        4-5-2014   ,         6-6-2015   ,           32
125    ,        20-02-2014 ,      4-4-2015      ,           7
123    ,        21-03-2014 ,    21-02-2015      ,       5
125    ,        18-05-2014 ,      4-4-2015      ,           7
128    ,        2-2-2014   ,        5-3-2015    ,           21
];

final:

load

AcctNo,

Orderdate,

Expdate,

item,

AcctNo as ACT2

Resident tmp

where not exists (ACT2,AcctNo)

order by AcctNo, Orderdate desc

; 

drop table tmp;

DROP FIELD ACT2;

[robert99]

Thanks

I never knew that peek could do this. That is pick up the last value by one field

I though it was just the last (or 2nd to alst value etc)