CODEWITHSUNDEEP
sqlsql-serverstored-procedures
No1Lives4Ever
No1Lives4Ever

Reputation: 6883

Dynamic TOP query

I'm have two tables: sites and pages. Each site connected to zero or more pages. Each site contain a [credits] value (number).

I'm want to make a query that: return N (equal to [credits]) pages, of website which have one or more [credits].

Something like this:

SELECT TOP (sites.Credits) pages.Id, sites.Id FROM sites, pages 
    WHERE sites.Credits > 0 AND sites.Id = pages.WebSites_Id

Note that each site can have a different value of [credits]. So, this TOP cannot be working globally.

Any solution?

Thank you

Upvotes: 0

Views: 19

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Don't use commas in the FROM clause. You are using TOP inappropriately, because it only applies to the entire result set. Instead, think row_number().

Here is one method using CROSS APPLY:

SELECT TOP (sites.Credits) pages.Id, sites.Id
FROM sites s CROSS APPLY
     (SELECT p.*, row_number() over (order by (select null)) as seqnum
      FROM pages p
      WHERE p.Id = p.WebSites_Id
     ) p
where seqnum <= s.credits;

Upvotes: 2

Related Questions