January 11, 2019 at 11:17 am
TableOfData has 50 some columns. I want to grab 20 rows (doesn't matter which ones) for certain values in the column "SERVICE_TYPE_CD". Service_Type_CD column has various values, but I only want the values that match the values in the column Approved from the table Measures. I tried this below and it only gives me top # rows in "general". I believe I need some kind of subquery or for loop?
SELECT TOP (20) * FROM [TableOfData]
where SERVICE_TYPE_CD in (Select Approved from Measures)
order by SERVICE_TYPE_CD asc
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
January 11, 2019 at 11:25 am
So you want the top 20 for each different value of 'Approved'?
January 11, 2019 at 12:13 pm
One way is to use Row_Number
select * from (
select *, Row_number() over (partition by ServiceTypeCd order by ServiceTypeCd) RowNum
from [TableOfData]
where SERVICE_TYPE_CD in (Select Approved from Measures)) x
where Rownum <= 20
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 12, 2019 at 7:45 am
usererror - Friday, January 11, 2019 11:17 AMTableOfData has 50 some columns. I want to grab 20 rows (doesn't matter which ones) for certain values in the column "SERVICE_TYPE_CD". Service_Type_CD column has various values, but I only want the values that match the values in the column Approved from the table Measures. I tried this below and it only gives me top # rows in "general". I believe I need some kind of subquery or for loop?
SELECT TOP (20) * FROM [TableOfData]where SERVICE_TYPE_CD in (Select Approved from Measures)
order by SERVICE_TYPE_CD asc
You query looks like it is doing what you've specified. Can you could expand on what it's not doing that it should be doing?
January 12, 2019 at 8:16 am
Phil Parkin - Friday, January 11, 2019 11:25 AMSo you want the top 20 for each different value of 'Approved'?
Essentially, yes. The Service_type_CD column has 40 some distinct values but I only want 20x records of the values from Service_Type_CD that match values stored in the Approved column of another table.
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
January 12, 2019 at 8:17 am
Mike01 - Friday, January 11, 2019 12:13 PMOne way is to use Row_Number
select * from (
select *, Row_number() over (partition by ServiceTypeCd order by ServiceTypeCd) RowNum
from [TableOfData]
where SERVICE_TYPE_CD in (Select Approved from Measures)) x
where Rownum <= 20
That did it! Thanks!
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
January 12, 2019 at 8:29 am
usererror - Saturday, January 12, 2019 8:17 AMMike01 - Friday, January 11, 2019 12:13 PMOne way is to use Row_Number
select * from (
select *, Row_number() over (partition by ServiceTypeCd order by ServiceTypeCd) RowNum
from [TableOfData]
where SERVICE_TYPE_CD in (Select Approved from Measures)) x
where Rownum <= 20That did it! Thanks!
I see, so you wanted the TOP(20) of each different SERVICE_TYPE_CD that's in the Measures table. These queries will also do the job:
If Approved is already distinct on table dbo.Measures:select X.*
from dbo.Measures M
cross apply(SELECT TOP(20) *
FROM dbo.[TableOfData] T
WHERE T.SERVICE_TYPE_CD = M.Approved) X
order by X.SERVICE_TYPE_CD
If Approved is not distinct on table dbo.Measures:;with CTE AS
(
Select distinct Approved from Measures
)
select X.*
from CTE
cross apply(SELECT TOP(20) *
FROM [TableOfData] T
WHERE T.SERVICE_TYPE_CD = CTE.Approved) X
order by X.SERVICE_TYPE_CD
January 12, 2019 at 8:50 am
Thanks, I'm now starting to venture into areas where my beginner's knowledge is reaching its limits!
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply