quote:

---

Originally posted by stixoffire

I have a table of products, and many products have the same name only they are different sizes. I want to only select a particular size like 20 oz, but if it does not exist in that size I want to select the next appropriate size (and which case if it does not exist i wish to select the next appropriate size and so on ..
Does any one know how I can accomplish this and would be willing to help me out by posting some code, some direction ??

---

quote:

---

Originally posted by mahesh_bote

quote:

---

Originally posted by stixoffire

I have a table of products, and many products have the same name only they are different sizes. I want to only select a particular size like 20 oz, but if it does not exist in that size I want to select the next appropriate size (and which case if it does not exist i wish to select the next appropriate size and so on ..
Does any one know how I can accomplish this and would be willing to help me out by posting some code, some direction ??

---

and what if the size does exists then? whether search should continue? If not then try IN in your query. Like

Select {some cols} From {table} Where size IN {size1, size2, sizen}

confirm on this.

Mahesh

---

quote:

---

Originally posted by mahesh_bote

If possible, post some real life data with desired o/p so that I can dig around.

Mahesh

---

quote:

---

Originally posted by LoztInSpace

something like this?:

select * from products p1
where sku=@x and size=(select max(@s) from products p2 where p1.sku=p2.sku)

replace max with min if you want to go up or down.

---

SELECT t.ProdName,t.sizename
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY ProdName ORDER BY CAST(LEFT(sizename,LEN(sizename)-1) AS float) ) AS RowNo,
 *
FROM Table
)t
WHERE t.Category=100
AND t.RowNo=1

quote:

---

Originally posted by visakh16

May be this:-

SELECT t.ProdName,t.sizename
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY ProdName ORDER BY CAST(LEFT(sizename,LEN(sizename)-1) AS float) ) AS RowNo,
 *
FROM Table
)t
WHERE t.Category=100
AND t.RowNo=1

---

SELECT 
	P.*
FROM 
	@Product AS P
INNER JOIN
	(
		SELECT
			ProdName,
			MIN(Sku) AS Sku,
			MIN(ABS(CAST(LEFT(sizename, LEN(sizename) - 1) AS FLOAT) - CAST(.5 AS FLOAT))) AS Diff
		FROM 
			@Product
		GROUP BY
			ProdName
	) AS T
	ON T.ProdName = P.ProdName
	AND T.Sku = p.Sku

quote:

---

Originally posted by Lamprey

Visakh's solution might work for you. But, I put this together real quick incase you are trying to get the next closest size:

SELECT 
	P.*
FROM 
	@Product AS P
INNER JOIN
	(
		SELECT
			ProdName,
			MIN(Sku) AS Sku,
			MIN(ABS(CAST(LEFT(sizename, LEN(sizename) - 1) AS FLOAT) - CAST(.5 AS FLOAT))) AS Diff
		FROM 
			@Product
		GROUP BY
			ProdName
	) AS T
	ON T.ProdName = P.ProdName
	AND T.Sku = p.Sku

Obviously, I left out the filter on category. But, I figure you cna add that if need be.

---

SELECT
	P.ProdName,
	P.sizename,
	MIN(P.sku)
FROM 
	@Product AS P
INNER JOIN 
	(
		SELECT
			ProdName,
			MIN(ABS(CAST(LEFT(sizename, LEN(sizename) - 1) AS FLOAT) - CAST(.5 AS FLOAT))) AS Diff
		FROM 
			@Product
		GROUP BY
			ProdName
	) AS T
	ON P.ProdName = T.ProdName
	AND ABS(CAST(LEFT(P.sizename, LEN(P.sizename) - 1) AS FLOAT) - CAST(.5 AS FLOAT)) = t.Diff
GROUP BY
	P.ProdName,
	P.sizename