0

This question is geared for those who have more SQL experience than me.

I am writing a query(that will eventually be a Stored Procedure but this should be irrelevant) where I want to select the count of rows if the most recent entry's is equivalent to the one that was just entered before. And i want to continue to do this until it hits an entry that has a different value. (Poorly explained so I will show the example)

In my table I have a column 'Product_Id' and when this query is run i want it take the product_id and compare it to the previously entered product Id, if its the same I want to add one, and I want it to keep checking the previously entered product_id until it runs into a different product_id

I'm hoping it sounds more complicated than it is, and the query would look something like

Select count(Product_ID)
FROM dbo.myTable
Where Product_Id = previous(Product_Id)

Now, i know that previous isn't a keyword in TSQL, and neither was Last, but I'm hoping of someone who knows a keyword that does what I am asking.

  • Edit for Sam

     USE DbName;
 GO
WITH OrderedCount as
(
select ROW_NUMBER() OVER (Order by dbo.Line_Production.Run_Date DESC) as RowNumber,
Line_Production.Product_ID
From dbo.Line_Production
)
Select RowNumber, COUNT(OrderedCount.Product_ID) as PalletCount
From OrderedCount
WHERE OrderedCount.RowNumber + 1 = RowNumber
and Product_ID = Product_ID
Group by RowNumber

The OrderedCount portion works, and it returns the data back how I want it, I'm now having trouble comparing the Product_ID's for different RowNumbers

my Where Clause is wrong

Brandon
  • 915
  • 4
  • 23
  • 44
  • 1
    Your question is extremely unclear, can you post some sample data and the expected results? Ideally, post `CREATE TABLE` and `INSERT` statements that others can just copy and paste to test with. – Pondlife Dec 12 '12 at 15:45
  • I can't really add the Inserts, because this is used in my vb.net application and a stored procedure adds one row at a time as necessary. – Brandon Dec 12 '12 at 15:51

2 Answers2

2

There's no keyword. That would be a nice magic solution, but it doesn't exist, at least in part because there is no guaranteed ordering (okay, you could have the keyword only if there is an ORDER BY...). I can write you a query, but that'll take time, so for now I'll give you a few steps and I'll come back and see if you still need help in a bit.

  1. Figure out an ORDER BY, otherwise no order is guaranteed. If there is a time entered field, that's a good choice, or an index, that works too.

  2. Learn to use Row_Number.

  3. Compare the table (with Row_Number) to itself where instance1.row - 1 = instance2.row.

Sam DeHaan
  • 10,246
  • 2
  • 40
  • 48
  • After looking into this a little more it makes sense. The only part I'm stuck on is point 3) the instance part. How do I compare instances? - See my Edit to the question for more – Brandon Dec 12 '12 at 17:33
  • 1
    @BrandonJ You have two table references. I can't think if instances is the right word for SQL. Something like `From OrderedCount o1, OrderedCount o2, WHERE o1.RowNumber + 1 = o2.RowNumber` – Sam DeHaan Dec 12 '12 at 17:53
  • Didn't full fix my issue, but it def started me down the right track, Thank you sir! – Brandon Dec 12 '12 at 18:02
0

If product_id is an identity column, couldn't you just do product_id - 1? In other words, if it's sequential, it's the same as using ROW_NUMBER mentioned in the previous comment.

sam yi
  • 4,806
  • 1
  • 29
  • 40