CODEWITHSUNDEEP
sqlnetezza
Srinivas Anupoju
Srinivas Anupoju

Reputation: 11

How to write a derived query in Netezza SQL?

I need to query the data for inviteid based. For each inviteid I need to have the top 5 IDs and ID Descriptions.

I see that the query I wrote is taking all the time in the world to fetch. I didn't notice an error or anything wrong with it.

The code is:

SELECT count(distinct ID),
  IDdesc,
  inviteid,
  A
FROM (
  SELECT
    ID,
    IDdesc,
    inviteid,
    RANK() OVER(order by  invtypeid asc ) A
  FROM Fact_s
    --WHERE dateid ='26012013'
  GROUP BY invteid,IDdesc,ID
  ORDER BY invteid,IDdesc,ID
) B
WHERE A <=5
GROUP BY A, IDDESC, inviteid
ORDER BY A

Upvotes: 1

Views: 2638

Answers (1)

user330315
user330315

Reputation:

I'm not sure I understood you requirement completely, but as far as I can tell the group by in the derived table is not necessary (just as the order by as Mark mentioned) because you are using a window function.

And you probably want row_number() instead of rank() in there.

Including the result of rank() in the outer query seems dubious as well.

So this leads to the following statement:

SELECT count(distinct ID),
       IDdesc,
       inviteid
FROM (
  SELECT ID,
         IDdesc,
         inviteid,
         row_number() OVER (order by invtypeid asc ) as rn
  FROM Fact_s
) B
WHERE rn <= 5
GROUP BY IDDESC, inviteid;

Upvotes: 1

Related Questions