Solved

return previous ID

Posted on 2009-04-09
7
290 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 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 26

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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

896 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now