The equivalent of cross apply and select top

cross-apply, join, select, sql, sql-server

Solution

Yes.

select
    CountryID, Country, CityID, CountryID, City
from
(

select Cities.*,
    Country,
    ROW_NUMBER() over (partition by cities.countryId order by cityid) rn

from 
cities
    inner join countries on cities.CountryID= countries.CountryID
) v
where rn<=3
and CountryID=1

Problem

I have table (script below): ``` use master go -- -- if db_id ( 'CrossApplyDemo' ) is not null drop database CrossApplyDemo go create database CrossApplyDemo go use CrossApplyDemo go -- if object_id ( 'dbo.Countries', 'U' ) is not null drop table dbo.Countries go create table dbo.Countries ( CountryID int, Country nvarchar(255) ) go -- insert into dbo.Countries ( CountryID, Country ) values ( 1, N'Russia' ), ( 2, N'USA' ), ( 3, N'Germany' ) , ( 4, N'France' ), ( 5, N'Italy' ), ( 6, N'Spain' ) go -- if object_id ( 'dbo.Cities', 'U' ) is not null drop table dbo.Cities go create table dbo.Cities ( CityID int, CountryID int, City nvarchar(255) ) go -- insert into dbo.Cities ( CityID, CountryID, City ) values ( 1, 1, N'Moscow' ), ( 2, 1, N'St. Petersburg' ), ( 3, 1, N'Yekaterinburg' ) , ( 4, 1, N'Novosibirsk' ), ( 5, 1, N'Samara' ), ( 6, 2, N'Chicago' ) , ( 7, 2, N'Washington' ), ( 8, 2, N'Atlanta' ), ( 9, 3, N'Berlin' ) , ( 10, 3, N'Munich' ), ( 11, 3, N'Hamburg' ), ( 12, 3, N'Bremen' ) , ( 13, 4, N'Paris' ), ( 14, 4, N'Lyon' ), ( 15, 5, N'Milan' ) go ``` I select cities groupping by countries, i can perform queries using two approaches: ``` -- using join: select * from CrossApplyDemo.dbo.Countries as countries inner join CrossApplyDemo.dbo.Cities as cities on cities.CountryID = countries.CountryID -- using apply select * from CrossApplyDemo.dbo.Countries as countries cross apply (select * from CrossApplyDemo.dbo.Cities as cities where cities.CountryID = countries.CountryID) as c; ``` Is where any query without "cross apply" which can return the same result as "apply query" below? : ``` select * from CrossApplyDemo.dbo.Countries as countries cross apply (select top(3) * from CrossApplyDemo.dbo.Cities as cities where cities.CountryID = countries.CountryID) as c; ``` the query: ``` select top(3) * from CrossApplyDemo.dbo.Countries as countries inner join CrossApplyDemo.dbo.Cities as cities on cities.CountryID = countries.CountryID ``` not work

Original source