Grouping is sorted according to a column, sorted by Shopid, Createtime, row_number () ordinal select No =row_number () over (partition by Shopid ORDER by Createt IME desc), * from Goods_info

Over cannot be used alone, with analytic functions: Rank (), Dense_rank (), Row_number (), and so on.
Its parameters: Over (partition by columnname1 ORDER by Columnname2)
Meaning: Group by the field specified by Columname1, or by the value of field columnname1 to sort by group.
For example: In the Employees table, there are two records for the department: department_id = 10 and 20
Select Department_id,rank () over (partition by department_id order by salary) from employees is the ranking of salaries in department 10, Make a salary ranking in department 20. If it is partition by org_id, it is ranked throughout the company.

The following are personal insights:

The over function in SQL and the Row_numbert () function work together to generate line numbers. You can sort the values of a column by grouping the rows of data that have the same value.

Execute statement: Select Row_number () over (order by AID DESC) as rowid,* from BB

SELECT
House.houseid,
House.housename,
House.iconflag,
House.orderid,
HOUSE.HOUSEJINGYINGFW,
House.housetel,
House.housecelphone,
Dbo.fngetdistance (
118.328213, 35.081728, House.longitude,
House.latitude
) as JL,
Goods.originalprice as Levelcount,
Goods.presentprice as Levelamount
From
House
Left JOIN (
Select Shopid,originalprice,presentprice
From (select No =row_number () up (partition by shopid ORDER BY createtime Desc), * from goods_info WHERE isclear = 1) t
where no=1
) Goods on shopid = House.houseid
where
(
House.isdel is null
or House.isdel = 0
)
and house.status = 1
and House.housestatus <> 0
and house.housetype like '%1% '
ORDER BY
House.orderid Desc,
JL ASC

IF object_id (' dbo.fngetdistance ') is not NULL
DROP FUNCTION dbo.fngetdistance
GO

--Calculates the distance between two coordinate points (longitude, latitude) of the Earth SQL function
CREATE FUNCTION [dbo]. [Fngetdistance] (@LatBegin Real, @LngBegin Real, @LatEnd Real, @LngEnd Real) RETURNS FLOAT
As
BEGIN
--distance (km)
DECLARE @Distance REAL
DECLARE @EARTH_RADIUS REAL
SET @EARTH_RADIUS = 6378.137
DECLARE @RadLatBegin Real, @RadLatEnd Real, @RadLatDiff real, @RadLngDiff Real
SET @RadLatBegin = @LatBegin *pi ()/180.0
SET @RadLatEnd = @LatEnd *pi ()/180.0
SET @RadLatDiff = @RadLatBegin-@RadLatEnd
SET @RadLngDiff = @LngBegin *pi ()/180.0-@LngEnd *pi ()/180.0
SET @Distance = 2 *asin (SQRT (POWER (SIN (@RadLatDiff/2), 2) +cos (@RadLatBegin) *cos (@RadLatEnd) *power (SIN (@RadLngDiff/2 ), 2)))
SET @Distance = @Distance * @EARTH_RADIUS
--set @Distance = Round (@Distance * 10000)/10000
RETURN @Distance
END
GO

 sql Group Group Gets the top N method implementation

  has a product table, contains id,name,city,addtime four fields, because the report needs to be grouped by city, to count the latest 10 products in each city, we inserted 1 million data into the table, and made the following series of tests:  www.2cto.com  create TABLE [dbo]. [Products] (    [id] [int] IDENTITY () not null,    [name] [nvarchar] (null,    [Addtime] [Datetim E] null,    [city] [nvarchar] (Ten) Null, constraint [pk_products] PRIMARY KEY clustered  (    [ID] ASC) with (Pad_index  = off, Statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks &nb sp;= on, Allow_page_locks  = on) on [PRIMARY]) on [Primary] 1, using the Row_number method, executed 5 times, averaging 8 seconds or so, the fastest.   www.2cto.com  select No, id,name,city from   (select  no =row_number () over (partition by City O Rder by addtime Desc), * FROM products) Twhere no< One order by City Asc,addtime DESC2, using the cross apply method, executed 3 times, basically in 3 minutes 5 seconds above, has It was very slow.  select distinct b.id,b.name,b.city from products A cross apply (select Top Ten * FROM products where City = A.ci Ty ORDER by  addTime desc) B3, with Count query, executed only two times, the first execution to 5 minutes, cancel the task execution, the second execution to 13 minutes, did not hold and directly stopped, it is unbearable.  select id,name,city from Products A where ( select count (city) from products where a.city = City and ADDT Ime>a.addtime) < 10order by the city Asc,addtime DESC4, using the cursor method, this last test, executed 5 times, each time is 10 seconds to complete, feel good.   declare @city nvarchar () CREATE TABLE #Top (id int,name nvarchar, City nvarchar (ten), Addtime datetime) DECLARE mycursor cursor Forselect  distinct city from Products order by City Ascopen Mycursorfetch next from MyCursor into @citywhile @ @fetch_status =0begin    INSERT INTO #Top      Select Top id,name,city,addtim E from products where city = @city      FETCH NEXT from MyCursor to @cityend  close mycursordeallocate Mycursorselect * from #Top order by City asc,addtime descdrop Table #Top   This comparison is not difficult to find, in the face of the group to obtain the Top n scene, you can prefer the Row_numbe R, cursors cursor second, the other two basically do not consider, the data volume is not used at all.

Grouping is sorted according to a column, sorted by Createtime, row_number () ordinal select No =row_number () over (partition by Shopid ORDER by Crea Tetime desc), * from Goods_info