SQLTeam.com | Weblogs | Forums

Select top 20 records per fund basis

hi

i want to select the top 20 records by largest value in a column from each fund in a table. whats the best way to do that.

You can use Row_number() over (partition by fund order by column desc), then select where the Row_number <= 20. If you provide DDL and sample data, we could provide some code for you

ya thought i might need that all right.

i have done this much so far

SELECT *,

       (ROW_NUMBER() OVER (PARTITION BY Fundcode
        ORDER BY Proceeds DESC)) AS RowNumber

FROM [dbo].[WorkingDataloadFile5]
where RowNumber<=20

where i get stuck with these is to tie in the rownumber they way i have it here is wrong as it just errors out. i need another select i think but not sire. also i want to enter all the columns i get back into [WorkingDataloadFile6] table has the same exact same columns

select * from 
( SELECT *,
       (ROW_NUMBER() OVER (PARTITION BY Fundcode
        ORDER BY Proceeds DESC)) AS RowNumber
FROM [dbo].[WorkingDataloadFile5]) v
where RowNumber<= 20

actually got it working way trying to make it complicated for some reason

SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Fundcode ORDER BY Proceeds DESC) AS Row,*
FROM [dbo].[WorkingDataloadFile5] ) us
WHERE us.Row <=20

it is just a simple insert after ?

thanks for reply ha. i just posted again as you did. sorry for wasting you time but thanks so much for the help

like this is not a great way to insert the data into a new table is it?

INSERT into [dbo].[WorkingDataloadFile6]
([AccountPeriodEnd]
,[SecurityName]
,[FundCode]
,[Bought]
,[Sold]
, [PurchaseCost]
,[Proceeds]
,[CategoryTypeCodeList]
,[CategoryCodeList]

       )

SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Fundcode ORDER BY Proceeds DESC) AS Row,
[AccountPeriodEnd]
,[SecurityName]
,[FundCode]
,[Bought]
,[Sold]
, [PurchaseCost]
,[Proceeds]
,[CategoryTypeCodeList]
,[CategoryCodeList]
FROM [dbo].[WorkingDataloadFile5] ) us
WHERE us.Row <=20

plus it does error out saying

Msg 121, Level 15, State 1, Line 5
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

is that because of the row

its the fundcode that the causing the error.

but still getting Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.

both tables are copies of each other

be careful with select *. Should list out the column names

INSERT into [dbo].[WorkingDataloadFile6]
([AccountPeriodEnd]
,[SecurityName]
,[FundCode]
,[Bought]
,[Sold]
, [PurchaseCost]
,[Proceeds]
,[CategoryTypeCodeList]
,[CategoryCodeList]

       )
SELECT [AccountPeriodEnd]
,[SecurityName]
,[FundCode]
,[Bought]
,[Sold]
, [PurchaseCost]
,[Proceeds]
,[CategoryTypeCodeList]
,[CategoryCodeList]
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Fundcode ORDER BY Proceeds DESC) AS Row,
[AccountPeriodEnd]
,[SecurityName]
,[FundCode]
,[Bought]
,[Sold]
, [PurchaseCost]
,[Proceeds]
,[CategoryTypeCodeList]
,[CategoryCodeList]
FROM [dbo].[WorkingDataloadFile5] ) us
WHERE us.Row <=20

ok thanks. will make sure not to use select * didnt want to anyway. only need to insert into a few columns

the select * was the reason it was failing. Select * included the row_number column which isn't in the table.

great thanks again for the help.