Get the min and max - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Get the min and max

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-01-12 : 03:17:41

I had this data....


SELECT '1' as theID, '0001' as OrderNo, 'AAA' as Item, '5' as QTY
UNION ALL
SELECT '2', '0001', 'AAA', '10' as QTY
UNION ALL
SELECT '3', '0001', 'AAA', '15' as QTY
UNION ALL
SELECT '4', '0002', 'AAd', '10' as QTY
UNION ALL
SELECT '5', '0002', 'AAd', '15' as QTY
UNION ALL
SELECT '5', '0002', 'AAd', '25' as QTY
UNION ALL
SELECT '6', '0003', 'AAc', '15' as QTY
UNION ALL
SELECT '7', '0003', 'AAc', '20' as QTY
UNION ALL
SELECT '8', '0003', 'AAc', '30' as QTY

Now I want to get the QTY based on "Max" and "Min" of their "theID"

Result should look like this...


OrderNo		Item	qtyMIN	qtyMAX
--------------------------------------------
001		AAA	5	15
002		AAd	10	25
003		AAc	15	30

Want Philippines to become 1st World COuntry? Go for World War 3...

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-12 : 03:36:43

Do you want the item name displayed should be of minimum qty item?
See if this is what u require
select a.orderno, a.item, qtymin,qtymax
from Tablename a
inner join
( select orderno, min(qty) qtymin, max(qty) qtymax
from Tablename
group by orderno
)b
on a.orderno = b.orderno and a.qty = b.qtymin

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-01-12 : 03:50:11

tnx shallu1_gupta!

but result should be the QTY based on "Max" and "Min" of their "theID"

please see my desired result.

tnx

Want Philippines to become 1st World COuntry? Go for World War 3...

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-01-12 : 04:12:25

my approach is this...


select CAST(theID as int) as theID, OrderNo, Item, QTY
into #temp
from (
	SELECT '1' as theID, '0001' as OrderNo, 'AAA' as Item, '5' as QTY
	UNION ALL
	SELECT '2', '0001', 'AAA', '10' as QTY
	UNION ALL
	SELECT '3', '0001', 'AAA', '15' as QTY
	UNION ALL
	SELECT '4', '0002', 'AAd', '10' as QTY
	UNION ALL
	SELECT '5', '0002', 'AAd', '15' as QTY
	UNION ALL
	SELECT '6', '0002', 'AAd', '25' as QTY
	UNION ALL
	SELECT '7', '0003', 'AAc', '15' as QTY
	UNION ALL
	SELECT '8', '0003', 'AAc', '20' as QTY
	UNION ALL
	SELECT '9', '0003', 'AAc', '30' as QTY
     ) AS T

SELECT 
	  t.orderno
	, qtyMIN = (select qty from #temp where theID = min(t.theID))
	, qtyMAX = (select qty from #temp where theID = max(t.theID))
FROM #temp AS t
GROUP BY t.orderno


drop table #temp

Any enhancement?

Want Philippines to become 1st World COuntry? Go for World War 3...

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-12 : 04:35:09

is this not satisfying ur requirement?

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-01-12 : 04:40:56

sorry to inform shallu1_gupta your approach is doesn't meet my needs.

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-12 : 04:45:18

[code]SELECT
t.OrderNo
, t.Item
, qtyMIN = (select top 1 QTY from #temp x where x.OrderNo = t.OrderNo order by theID, QTY)
, qtyMAX = (select top 1 QTY from #temp x where x.OrderNo = t.OrderNo order by theID DESC, QTY DESC)
FROM #temp AS t
GROUP BY t.OrderNo, t.Item[/code]
EDIT in RED
-----------------
'KH'
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.

avishivani
Starting Member

4 Posts

Posted - 2006-01-12 : 05:00:49

select orderno, item, min(qty) qtyMin, max(qty) qtyMax
from
(
SELECT '1' as theID, '0001' as OrderNo, 'AAA' as Item, '5' as QTY
UNION ALL
SELECT '2', '0001', 'AAA', '10' as QTY
UNION ALL
SELECT '3', '0001', 'AAA', '15' as QTY
UNION ALL
SELECT '4', '0002', 'AAd', '10' as QTY
UNION ALL
SELECT '5', '0002', 'AAd', '15' as QTY
UNION ALL
SELECT '5', '0002', 'AAd', '25' as QTY
UNION ALL
SELECT '6', '0003', 'AAc', '15' as QTY
UNION ALL
SELECT '7', '0003', 'AAc', '20' as QTY
UNION ALL
SELECT '8', '0003', 'AAc', '30' as QTY
) a
group by orderno, item
order by 1,2

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-01-12 : 05:11:48

wrong result avishivani.

result should be....


OrderNo		Item	qtyMIN	qtyMAX
--------------------------------------------
001		AAA	5	15
002		AAd	10	25
003		AAc	15	30

Want Philippines to become 1st World COuntry? Go for World War 3...

avishivani
Starting Member

4 Posts

Posted - 2006-01-12 : 05:26:59

Oops! I missed your something basic in your question.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources