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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy