Row count question

• James Halligan

  SSC Rookie

  Points: 28

  More actions

  September 14, 2005 at 4:45 pm

  #88986

   

  Folks,

  I'm looking to modify the SQL below to develop a temp table with a row count. At the moment it assigned a counter for every record in the 'sales' table regardless of any filter in the where clause. Can anyone help me get the counter to only include those records as specified by the where clause. You see the row counter starts at 4 because there are 3 records where order <'6871'

  I have used the 'pubs' database from SQL server

  use pubs

  select a1.ord_num,count(a2.ord_num)as counter

  from(select sales.ord_num,count(sales.ord_num)as jcount from sales group by sales.ord_num)a1,

  (select sales.ord_num,count(sales.ord_num)as jcount from sales group by sales.ord_num)a2

  where a1.ord_num>=a2.ord_num and a1.ord_num >'6871'

  group by a1.ord_num, a1.jcount

  this would really dig me out of a hole, so thanks in advance

  regards

  James

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  September 14, 2005 at 5:24 pm

  #589969

  Please don't Cross Post

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=145&messageid=219894