SELECT min(x.radar_queue_key), max(x.radar_queue_key)
FROM (SELECT TOP 100 radar_queue_key FROM z_radar_queue WHERE radar_group_Code='SFV OTS' ORDER BY radar_queue_key asc) as x
Results:867 966
SELECT min(x.radar_queue_key), max(x.radar_queue_key)
FROM (SELECT TOP 100 radar_queue_key FROM z_radar_queue WHERE radar_group_Code='SFV OTS') as x
Results:867 1879
SELECT min(radar_queue_key), max(radar_queue_key)
FROM z_radar_queue
WHERE radar_group_Code='SFV OTS'
867 1879
What is interesting is that actual Min() =867 and actual max() =966 for the Top 100 rows.
Both of these queries return exactly the same rows in exactly the same order (even when flushing data cache) because radar_queue_key is the primary key on the table.
SELECT TOP 100 radar_queue_key FROM z_radar_queue WHERE radar_group_Code='SFV OTS' ORDER BY radar_queue_key asc
SELECT TOP 100 radar_queue_key FROM z_radar_queue WHERE radar_group_Code='SFV OTS'
Value of 1879 does not appear in either result set. The execution is completely different if the sql statements are used as a subquery.
Lesson here is always use an order by clause when fetching rows for aggregation + TOP xxxx
No comments:
Post a Comment