Aggregations with Min? - 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)

Aggregations with Min?

Author

Topic

henrikf
Starting Member

7 Posts

Posted - 2006-02-13 : 17:40:20

Hi!

I'm having trouble with the Min function. I have a set of data looking like this (this is a very small sample):

Order_No.....Order_Line.....Order_Suffix
---------------------------------------------------
100123........1.................1
100123........1.................2
100123........2.................0

I'm trying to find (by select) the unique combination of this order by first picking the lowest Order_Line by using MIN and then the lowest Order_Suffix, also by using MIN. I'm expecting a result looking like this:

Order_No Order_Line Order_Suffix
---------------------------------------------------
100123........1.................1

However, I'm getting a result containing to rows, like this:

Order_No Order_Line Order_Suffix
---------------------------------------------------
100123........1.................1
100123........1.................2

How do I solve this?

Best regards,

Henrik

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-13 : 17:46:10

Try this:


SELECT y.Order_No, y.Order_Line, MIN(Order_Suffix) AS Order_Suffix
FROM YourTable y
INNER JOIN
(
	SELECT Order_No, MIN(Order_Line) AS Order_Line
	FROM YourTable
	GROUP BY Order_No
) t
ON y.Order_No = t.Order_No AND y.Order_Line = t.Order_Line
GROUP BY y.Order_No, y.Order_Line

Tara Kizer
aka tduggan

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-14 : 03:49:17

Assuming suffix < @N (e.g. suffix is tinyInt so @N = 256):

declare @N bigint
set @N = 256

select order_no, min(order_line * @N + order_suffix)/@N, min(Order_line * @N + order_suffix)%@N
from table t
group by order_no

henrikf
Starting Member

7 Posts

Posted - 2006-02-14 : 06:49:48

I tested the solution from tkizer and it worked just as I wanted.

Thanks a lot!

//Henrik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources