Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

return previous ID

Posted on 2009-04-09
7
Medium Priority
?
302 Views
Last Modified: 2012-05-06
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?
0
Comment
Question by:jung1975
7 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 600 total points
ID: 24111120
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
 
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 400 total points
ID: 24111125
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
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 400 total points
ID: 24111131
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 200 total points
ID: 24111187
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
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 200 total points
ID: 24111198
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
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 600 total points
ID: 24111690
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
 
LVL 2

Assisted Solution

by:TejasShahMscIT
TejasShahMscIT earned 200 total points
ID: 24113749
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question