All geek questions in one place

SQL - returns a limited number of rows, but the number of rows in a row

Scenario: I need to extract information from a Visual FoxPro database; however, when running large queries against this, there is a tendency to lock the system. To eliminate this, we set limits that canceled the request if it has passed a certain time, and the number of rows that it will return is limited.

Is there a way to get a query with "SELECT TOP ###", but also return the actual number of rows found in the statement? Or is this the only way to run a query twice? (the reason is that we can still fulfill the request, but we inform the user about what is happening, that is, "The first ### displays ### found items").

My initial research was simply to add “COUNT (*)” to the instruction selection part, but it didn’t quite fulfill what I was looking for (it returned the correct number of rows, but only one row was returned for the rest of the data).

+4
source share
2 answers

If I understand the question correctly, you can make a subquery, but that will mean that you are invoking an SQL account for each row returned:

select top 10 field1, field2, (select count(*) from table) as totalrows from table

This will give you the top 10 rows with an extra column in each named totrows containing a count of all the rows in the table.

Personally, however, I just run a separate query to get the top n lines and count.

+2
source

You will need to run 2 separate SELECTs. One, to get the COUNT rows returned by the query, and then return a subset of the entries for a particular page.

You can optimize this only by getting a full COUNT once when the first "page" is retrieved (ie does not do the whole count for subsequent pages)

+2
source

Source: https://habr.com/ru/post/1300297/

More articles:


All Articles