return previous ID

I have a table like below

ID    Product_ID
1   1001
1    1002
1     1003
1    1004
2    1008
2    1009

I am trying to write a query that return a previous product number based on the ID and productId that will be provided as parameters
For example, if I put 1 and 1002 , the query should return 1001
if i put 1 and 1004 then it should return 1003
if i put 2 and 1008 m then it should return 0 because it does not have a previuos product ID
what is the best way to do this SQL 2005?
jung1975Asked:
Who is Participating?
 
käµfm³d 👽Connect With a Mentor Commented:
Try:
@id int;  // illustration
@pid int; // illustration
 
IF (SELECT COUNT(1) FROM prodTbl WHERE product_id < @pid AND id = @id) > 0
   SELECT TOP 1 product_id FROM prodTbl WHERE product_id < @pid AND id = @id ORDER BY product_id DESC;
ELSE
   SELECT 0;

Open in new window

0
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
Try this as a query.  I was not sure from your question if you just wanted a query or needed it in a SP.  I can show you how to do that if you need.
declare @ID int, @Product_ID int
set @ID = 1
set @Product_ID = 1002
 
select coalesce(max(Product_ID),0) as PrevProduct_ID
from table where ID = @ID
and Product_ID < @Product_ID

Open in new window

0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Try this one out:

Replace ? with your input values.
WITH CTE(ID, Product_ID, rnum) as (
SELECT ID, Product_ID, ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY Product_ID ) rnum
from urtable )
SELECT CASE WHEN t1.rnum <> 1 THEN ( SELECT t2.Product_ID from CTE t2 where
t2.rnum = t1.rnum - 1 ) ELSE '0' END as Product_ID
FROM CTE t1
WHERE t1.ID = ?
AND t1.Product_ID = ?

Open in new window

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
8080_DiverConnect With a Mentor Commented:
I have constructed this SQL Snippet as though it were going to be put in a Stored Procedure.
Essentially, the @ID and @Product_ID are where you would put your desired parameters.
Not having any data to test with, I can only say that this should work.

SELECT COALESCE(MAX(Product_ID), 0) AS Previous_Product_ID
FROM   PRODUCT_TABLE
WHERE  ID = @ID
  AND  Product_ID < @Product_ID;

Open in new window

0
 
JestersGrindConnect With a Mentor Commented:
Try this

Greg



DECLARE @ID INT, @Product_ID INT
 
SELECT @ID = 1, @Product_ID = 1003
 
;WITH CTE1
AS
(
SELECT ID, Product_ID, ROW_NUMBER() OVER(ORDER BY ID, Product_ID)AS RowNum
FROM Test
WHERE ID = @ID
),
CTE2
AS
(
SELECT ID, Product_ID, RowNum
FROM CTE1
WHERE Product_ID = @Product_ID
)
SELECT @ID, ISNULL(b.Product_ID, 0)
FROM CTE2 a LEFT OUTER JOIN 
	   CTE1 b ON a.RowNum - 1 = b.RowNum 

Open in new window

0
 
käµfm³d 👽Connect With a Mentor Commented:
Forgot a closing paren:
@id int;  // illustration
@pid int; // illustration
 
IF (SELECT COUNT(1) FROM prodTbl WHERE product_id < @pid AND id = @id) > 0)
   SELECT TOP 1 product_id FROM prodTbl WHERE product_id < @pid AND id = @id ORDER BY product_id DESC;
ELSE
   SELECT 0;

Open in new window

0
 
TejasShahMscITConnect With a Mentor Commented:
Hi,

try this:


WITH cte as (
SELECT	ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY Product_ID ) AS RowID,
		ID, 
		Product_ID
FROM TableName )
SELECT	t1.ID,
		ISNULL(t2.Product_ID,0) AS Product_ID
FROM CTE t1
LEFT JOIN cte t2 ON t1.ID = t2.ID
	AND t1.RowID = t2.RowID - 1
WHERE t1.ID = @ID
AND t1.Product_ID = @Product_ID

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.