How to return the lowest values ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to return the lowest values ?

Author

Topic

paulnamroud
Starting Member

26 Posts

Posted - 2012-08-31 : 10:26:24

Hello,

I have a table for Sku Price. And each Sku can be sold with different price based on the UOM.

So my target is to create a SQL Statement that returns:
- Only One Record for each Sku
- For each Sku returns the lowest available UOM in the table
- And, finally the assigned price

Here's a snapshot of my table:

Sku Id Price UOM
1 $11.95 EACH (***)
1 $18.95 PACK_OF_2
1 $29.95 PACK_OF_3

2 $2.95 EACH (***)
2 $4.95 PACK_OF_2
2 $29.95 PACK_OF_12

3 $15.95 PACK_OF_2 (***)
3 $34.95 PACK_OF_5

4 $19.95 PACK_OF_3 (***)

5 $22.95 EACH (***)

6 $7.95 EACH (***)
6 $14.95 PACK_OF_3

7 $19.95 PACK_OF_12 (***)
7 $84.95 PACK_OF_60

Here's the result that i'm expecting from the SQL Statement:

Sku Id Price UOM
1 $11.95 EACH
2 $2.95 EACH
3 $15.95 PACK_OF_2
4 $19.95 PACK_OF_3
5 $22.95 EACH
6 $7.95 EACH
7 $19.95 PACK_OF_12

Can you help me to do this SQL Statement ?

Thank you

Paul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 10:29:58

[code]
SELECT SKU,UOM,Price
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY UOM) AS Seq,*
FROM Table
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2012-09-04 : 11:14:46

quote:
Originally posted by visakh16

SELECT SKU,UOM,Price
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY UOM) AS Seq,*
FROM Table
)t
WHERE Seq=1

Wouldn't that be an ORDER BY Price instead?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-05 : 21:44:49

nope...as per your requirement stated below

For each Sku returns the lowest available UOM in the table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources