Solved

return previous ID

Posted on 2009-04-09
7
288 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 74

Accepted Solution

by:
käµfm³d   👽 earned 150 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 50 total points
Comment Utility
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
Comment Utility
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 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 150 total points
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

762 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

11 Experts available now in Live!

Get 1:1 Help Now