Solved

return previous ID

Posted on 2009-04-09
7
297 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 150 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 100 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 100 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 50 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 50 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 150 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 50 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

623 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