I'm trying to create an SQL query, but I only want to return a certain number of records per page, but I might also want to exceed that amount in certain circumstances.
Here's my situation:
I am listing addresses, and sorting them by city. I only want to show 20 per page, but if there are 3 cities, and 2 have 6 each, and the third has 10, there would be a total of 22 records I need to display on that page, because I don't want to split the records if they have the same city. And vice versa, there are 2 cities, and the first has say 15 entries, and the second has 14, I only want to show the first city, and have the second display on a new page. Is there a way to do this just with SQL, or am I going to have to program it? I'm guessing it'd be more on the programming side, and I have an idea of how to do it.
I've checked into using LIMIT, but I don't think MSSQL has that syntax built into it, and its a MySQL only deal. Is this true?
I'm thinking I would create a query to get the records, count how many per each city, add totals and display only a certain amount per page depending on city. Any other suggestions on how to get this done? Thanks.
Linear Mode
