CODEWITHSUNDEEP
c#sqlsql-server
CR41G14
CR41G14

Reputation: 5594

select top and a selected row in SQL

I have a league with 3000 entrants. What I want to do is select only 50 of these but if the user does not exist in this top 50 I want to select that row also and display their current position, the below query shows I am selecting the top 50 positions from the main inner query which brings back all players and their positions. Now I want to show the current logged in user and their position so is there any way to select top 50 and the user's entry from the subset by amending the below? I.e. is it possible to run two selects on a subset like

SELECT TOP 50 AND SELECT TOP 1 (Where condition)

FROM 
(
    Subset
)

my Query

SELECT TOP 50 [LeagueID],
       [EntryID],
       [UserID],
       [TotalPoints],
       [TotalBonusPoints],
       [TotalPointsLastEvnet],
       [TotalBonusPointsLastRaceEvent],
       [Prize],
       [dbo].[GetTotalPool]([LeagueID]) AS [TotalPool],
       DENSE_RANK() OVER( PARTITION BY [LeagueID] ORDER BY [TotalPoints] DESC, [TotalBonusPoints] DESC) AS [Position],
       DENSE_RANK() OVER( PARTITION BY [LeagueID] ORDER BY [TotalPointsLastRace] DESC, [TotalBonusPointsLastRace] DESC) AS [PositionLastRace]
FROM
(
    // inner query here bringing back all entrants

) AS DATA

Upvotes: 0

Views: 89

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can do this without a union. You just need or:

WITH data as (
    // inner query here bringing back all entrants
)
SELECT * -- or whatever columns you really want
FROM (SELECT data.*
             DENSE_RANK() OVER (PARTITION BY [LeagueID]
                                ORDER BY [TotalPoints] DESC, [TotalBonusPoints] DESC
                               ) AS [Position],
             DENSE_RANK() OVER (PARTITION BY [LeagueID]
                                ORDER BY [TotalPointsLastRace] DESC, [TotalBonusPointsLastRace] DESC
                               ) AS [PositionLastRace],
             ROW_NUMBER() OVER (PARTITION BY [LeagueID]
                                ORDER BY [TotalPoints] DESC, [TotalBonusPoints] DESC
                               ) as Position_Rownum
       FROM data
      ) d
WHERE Position_RowNum <= 50 or UserId = @Current_userid

This uses row_number() to do what you wanted top to do. I note that your question does not include an order by clause, so I am guessing that you want the ordering by Position.

Upvotes: 1

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

Please try the following:

;WITH entrants 
([LeagueID],[EntryID],[UserID],[TotalPoints],[TotalBonusPoints],[TotalPointsLastEvnet],
[TotalBonusPointsLastRaceEvent],[Prize],[TotalPool],[Position],[PositionLastRace])
AS
(
    SELECT [LeagueID],
           [EntryID],
           [UserID],
           [TotalPoints],
           [TotalBonusPoints],
           [TotalPointsLastEvnet],
           [TotalBonusPointsLastRaceEvent],
           [Prize],
           [dbo].[GetTotalPool]([LeagueID]) AS [TotalPool],
           DENSE_RANK() OVER( PARTITION BY [LeagueID] ORDER BY [TotalPoints] DESC, [TotalBonusPoints] DESC) AS [Position],
           DENSE_RANK() OVER( PARTITION BY [LeagueID] ORDER BY [TotalPointsLastRace] DESC, [TotalBonusPointsLastRace] DESC) AS [PositionLastRace]
    FROM
    (
        // inner query here bringing back all entrants

    ) AS DATA
)
SELECT TOP 50 * FROM entrants
UNION
SELECT * FROM entrants WHERE UserID = @current_userid
ORDER BY Position;

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

You can use a CTE instead of sub-query and SELECT TOP 50 and then SELECT TOP 1 and union all something like this....

;WITH CTE AS
 (
   // inner query here bringing back all entrants
 )
SELECT TOP 50 * FROM CTE WHERE <Some Codition>
UNION ALL 
SELECT TOP 1  * FROM CTE WHERE <Some other Codition>

Upvotes: 0

masum7
masum7

Reputation: 832

You can use union for joining two subset of results: http://msdn.microsoft.com/en-au/library/ms180026.aspx

Also if you need to populate result from same suset then you can use common table expression: http://technet.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx

The pseodocode:

with subset(...)

select top 50
union
select top 1

Upvotes: 1

Related Questions