HANA SQL Select Top Distinct Values

[#1]

SQL Experts,

I have a table with fields and values.

(This is a timesheet table and the workitem is the code on which the time was logged.)

ID (Key)         WORKITEM          ENTRYDATE     ENTRYTIME

1                    ITEM_1                 2016-01-01        14:00

2                    ITEM_1                 2016-01-01        14:00

3                    ITEM_1                 2016-01-01        14:00

4                    ITEM_2                 2016-01-02        14:00

5                    ITEM_8                 2016-01-02        14:00

6                    ITEM_3                 2016-01-02        14:00

7                    ITEM_4                 2016-01-03        14:00

8                    ITEM_8                 2016-01-03        14:00

9                    ITEM_8                 2016-01-03        14:00

10                  ITEM_9                 2016-01-04        14:00

I would need the last 5 unique workitems that were used, in an SQL Select.

Can anybody help with this because I tried a select distinct with a sort on entry date + time descending but this did not give me the correct result.

The expected result here would be:

     ITEM_9

     ITEM_8

     ITEM_4

     ITEM_3

     ITEM_2

Thanks in advance

[lbreddemann · Accepted solution]

Ok, this is rather standard SQL...

You can use an aggregate function to find the most current timestamp and group by workitem.

Based on this, all you need to do is to sort the groups by their MAX(TIMESTAMP) in descending order and take the first five.

One by one:

1) we need a complete timestamp for the ordering. In your table DATE and TIME are stored separately which means we have to combine them to get a full timestamp.

As SAP HANA doesn't allow a direct concatenation of date and time columns, a little trick is needed: calculate the number of seconds from midnight to the timestamp value und add these seconds to the date column:

add_seconds(ENTRYDATE, seconds_between ('00:00:00', ENTRYTIME))

2) with this we can perform the grouping, aggregation and sorting:

    SELECT WORKITEM

    FROM timesheet

    GROUP BY WORKITEM

    ORDER BY 

          MAX(add_seconds(ENTRYDATE, seconds_between ('00:00:00', ENTRYTIME))) desc

3) finally we only need the TOP 5 entries:

SELECT TOP 5 WORKITEM FROM (

    SELECT WORKITEM

    FROM timesheet

    GROUP BY WORKITEM

    ORDER BY  MAX(add_seconds(ENTRYDATE, seconds_between ('00:00:00', ENTRYTIME))) desc

)

Looks easy now, doesn't it?

[Former Member · Accepted solution]

Hi,

thank you for your time but I quickly realized it was a rather basic question ...

this is how I Fixed it.

/********* Begin Procedure Script ************/

BEGIN

  var_out =

  SELECT TOP 5 WORKPCKG, WORKITEM, NONPROJECTCODE,

  MAX(ENTRYDATE) as ENTRYDATE, MAX(ENTRYTIME) as ENTRYTIME

  FROM "DELAWARE"."delaware.global.data::ZTIMESHEET"

       WHERE ENTEREDBYPERNR = :IN_USER

       GROUP BY WORKPCKG, WORKITEM, NONPROJECTCODE;

END

/********* End Procedure Script ************/

[lbreddemann · Accepted solution]

And where exactly do you do the sorting of the values?

Also, by treating ENTRYDATE and ENTRYTIME separately your solution relies on the condition that the data for any combination of WORKPCKG, WORKITEM, NONPROJECTCODE can never cover more than one day.

If it did, you would end up with maximum values for both columns that don't belong together.

[Former Member · Accepted solution]

Hi,

So we picked this up again and fixed it according to your insight/trick.

SELECT TOP 5

  T1."WORKPCKG",

  T1."WORKITEM",

  coalesce(T1."NONPROJECTCODE",T1."NONPROJECTCODE", '') "NONPROJECTCODE",

  T2."WORKITEMNAME",

  T3."CUSTPROJWORKPACKAGENAME",

  T3."CUSTOMERPROJECT",

  T3."CUSTOMERPROJECTNAME",

  MAX(add_seconds(T1."ENTRYDATE", seconds_between('00:00:00', T1."ENTRYTIME"))) as TIME_STAMP

FROM "___"."___.global.data::ZTIMESHEET" AS T1

LEFT OUTER JOIN "_SYS_BIC"."___.global.models/ZIWRKPKGWRKITEM" AS T2

  ON T1."WORKPCKG" = T2."WORKPACKAGE"

  AND T1."WORKITEM" = T2."WORKITEM"

INNER JOIN "_SYS_BIC"."___.global.models/ZICPWORKPACKAGE" AS T3

  ON T2."WORKPACKAGE" = T3."CUSTOMERPROJECTWORKPACKAGE"

WHERE T1."USERID" = :IN_USER

GROUP BY T1."WORKPCKG",

  T1."WORKITEM",

  T1."NONPROJECTCODE",

  T2."WORKITEMNAME",

  T3."CUSTPROJWORKPACKAGENAME",

  T3."CUSTOMERPROJECT",

  T3."CUSTOMERPROJECTNAME"

ORDER BY TIME_STAMP DESC;