sql query

When you say minimum first 20 do you mean ignore the week if there's less than 20 and return them all for the week if there's more than 20?

When the records sorted by one of the column, if it having even 1 record also it should display.
I mean when the week records sorted by one column, just trying to collect only first 20 records.

select up to the first 20 records in week for all the weeks in year with a single query

Does that describe your need?

How is "first" determined? By a date/time column?

when the records are sorted by using one of the column, then will fetch only first 20 records

Hi Chen, thanks a lot for the quick solution. But date we are not suppose to hard code, that should be sysdate and the query should fetch the entire year data with sorted by one of the column in week and only to fetch first 20 records. So that it is easy to compare the data.

Thanks.

In such cases, sample data would have helped a lot.

and the "expected result" too.

Providing both sample data and expected result is the most effective way to ask a sql query related question.

thank a lot Vachhrajani. Let me try this and let you the results.

@Jayaprakash M - Do you need more help on this ?

Hi Pavan, we are trying on above solutions, if you have any better solution please provide me.
Thanks.

Yes sir, working on a solution for you ! you will get the same in some time.

Thanks a lot Pavan. Will try and let you know the results.

Great ! thanks bro !

Here Table is already having with more than 1 million records.

Assume that table name is emp, and column name need to be sorted with 'total' weekly, for whole year. In that case kindly simplify the query.

Thank lot in advance.

If you have a large number of rows, the CROSS APPLY in the proposed solution (ID: 41797901) will generate a large number of logical reads (since the inner statement will be need to be executed, i.e. "applied" for each row in the outer CTE).

Suggest you perform a performance evaluation of both solutions.

Be wary of claims such as "best solution" particularly if very few facts have been provided in the question.

Be wary of claiming a "best solution" until sufficient facts are known.

A single pass of the data is most likely to produce the best performance. So I recommend you try using the ROW_NUMBER() OVER () approach as it only requires a single pass through the data.

@Nakul Vachhrajani - In most of the cases CROSS APPLY will perform better as compared to inner join. Also ORDERING an entire data set is more costly the ordering chunks of data..

@author - compare the timings and stats before you implement any solution ! Enjoy

@author - Do you need more help with this question. If no, could you please  accept one answer as accepted solution and close the question.

The performance of any query depends upon the nature of the data structures, underlying data and the query itself. Hence, to say that CROSS APPLY will generally perform better may not be correct. CROSS APPLY works great when the output of the applied function depends upon the value of the rows in the outer query (i.e. the function needs to be calculated for, i.e. "applied to" each row separately). If this is not the case, a JOIN may be faster.

Also, when using TOP, one has to use ORDER BY. Without ORDER BY, the order of the output rows is not guaranteed.

Finally depending on the size of the data, I would rather take the performance hit of ordering once rather than once with each outer row (since SQL may need to spool out to the tempdb for the sorting and IO is always the costliest operation).

I suggest that the person claiming "best" should prove that claim, or refrain from making claims they are unwilling to prove.

There is no compelling need for cross apply in solving this question and a cross apply is not more efficient than an inner join (if that was true database vendors would not use inner joins would they?). The apply operator is a very valuable feature but it is not the only worthwhile feature.

@author - Do you need more help with this question. If no, could you please  accept one answer as accepted solution and close the question.

No further update from author while there is enough advice given to author.