I discovered a nasty bug in the way Sybase Iq last_value over partition works. More precisely it returns random values. On the other hand first_value is working fine so for now I am using that one instead, with reverse ordering in partitions. But it makes my queries a little harder to understand.
Try this eg:
Let's say we have table TestLastValueTable
TableId RequestId Sender MessageNumber Location
1 1 alan 2 London
2 1 mary 3 Paris
3 1 mike 1 Sevilla
4 2 gaga 2 Paris
5 2 gugu 3 Sevilla
When using the following query:
Select Id, requestId , last_value(sender) over (partition by requestId order by messageNum desc) as lastRequestSender ,
first_value(location) over (partition by requestId order by messageNum desc) as firstRequestLocation
From TestLastValueTable
order by Id
I expected the outcome to be
Id, requestId, lastRequestSender, firstRequestLocation
1, 1, 'mike', 'Paris'
2, 1, 'mike', 'Paris'
3, 1, 'mike', 'Paris'
4, 2, 'gaga', 'Bucharest'
5, 2, 'gaga', 'Bucharest'
And it actually is :
Id, requestId, lastRequestSender, firstRequestLocation
1, 1, 'alan', 'Paris'
2, 1, 'mary', 'Paris'
3, 1, 'mike', 'Paris'
4, 2, 'gaga', 'Bucharest'
5, 2, 'gugu', 'Bucharest'
UPDATE: This bug is related to the partition, changing the partition to being specified by "rows between unbounded preceding and unbounded following" (which should not make any difference) , makes last_value to return the correct value:
Select Id, requestId , last_value(sender) over (partition by requestId order by messageNum desc rows between unbounded preceding and unbounded following) as lastRequestSender ,
first_value(location) over (partition by requestId order by messageNum desc) as firstRequestLocation
From TestLastValueTable
order by Id