Select Top # with a Where Clause

  • 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.

  • So you want the top 20 for each different value of 'Approved'?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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/

  • usererror - Friday, January 11, 2019 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

    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?

  • Phil Parkin - Friday, January 11, 2019 11:25 AM

    So 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.

  • Mike01 - Friday, January 11, 2019 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

    That did it! Thanks!

    -Mark
    MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.

  • usererror - Saturday, January 12, 2019 8:17 AM

    Mike01 - Friday, January 11, 2019 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

    That 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

  • 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