Selecting single sorted row from many column

[Gary]

Hi
Been at this for 2 days now.
Each business has several packages which they can sort using
sort_order.
I'm trying to get one package for each business(that I can do), however
I want it to be the one with the lowest sort_order value
As you can see below the first record has sort_order=5 when it should
be 1.

Most of the sort_order columns will be zero by default
Any help so i can get on with my life!

Cheers
Gary

------------Current select-------------------
SELECT *
FROM dbo.testAccommodation_Packages T1
WHERE (NOT EXISTS
(
SELECT *
FROM testAccommodation_Packages
WHERE business_id = T1.business_id AND Package_ID < T1.Package_ID)
)

--------------results:-----------------------
Package_ID business_id item_name sort_order
1 2 3rd Night FREE ... 5
11 3 Donegal Town ... 0
20 4 Executive ... 0

--------------To recreate----------------------
CREATE TABLE [testAccommodation_Packages] (
[Package_ID] [int] IDENTITY (1, 1) NOT NULL ,
[business_id] [int] NULL ,
[Item_Name] [nvarchar] (300) NOT NULL ,
[sort_order] [int] NULL CONSTRAINT
[DF_Accommodation_Packages_sort_order] DEFAULT (0),

)
-------------------------------------------------
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('1','2','3rd Night FREE when you stay 2 nights MIDWEEK (129 Euro
PPS)','5')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('2','2','Selected Donegal Town Hotel Weekend Sale - 2 B&B and 1
Dinner Only € 129 PPS','4')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('3','2','2 Night Specials -Jan, Feb & Mar 2 B&B and 1 Dinner 149
Euro PPS','3')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('4','2','Easter Hotel Breaks in Donegal Town - 2 B&B + 1 D
€169pps','2')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('5','2','2005 Bluestack Hillwalking, 2 nights B&B, 1 Dinner, 5
course Lunch 159 Euros PPS (~109 Stg)','1')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('6','2','April Pamper Package - 2 Night Special ONLY
€195pps','10')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('7','2','Discount Hotel Prices for 8th & 9th April Only € 119
PPS','7')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('8','2','Golden Year Breaks in Donegal - 4B&B + 2 Dinner
€229pps','8')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('9','2','Hotel Summer Breaks Sale in Donegal - 2B&B + 1 Dinner
€169pps','9')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('10','2','STAY SUNDAY NIGHTS FOR €25PPS','6')
INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('11','3','Donegal Town Midweek Special 99 Euro PPS 3 Nights B&B
','0')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('12','3','Bridge Weekend 2 nights B&B 79 Euro PPS (approx 55
Stg) Double Room','0')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('13','3','Donegal Spring Weekend Specials 2 B&B 1 Dinner
109.00euros pps','0')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('14','3','Valentines Weekend 2 nights B&B and 1 four course
gourmet dinner 99Euro PPS','0')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('19','3','Golden Years Break.40% OFF 4 nights B&B
€129.00p.p.s.','0')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('20','4','Executive Celebration Offer 1 night B&B + Dinner €
139 PPS','0')

INSERT
testAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)
VALUES('21','4','Watercolour Painting Break 3 B&B Full Board and
Tuition € 335 PPS','0')

[Gary]

Hi lads
Got it sorted using one of these for ref:

SELECT *
FROM Accommodation_Packages a
WHERE (Package_ID =
(SELECT MIN(Package_ID)
FROM Accommodation_Packages AS b
WHERE b.business_id = a.business_id
AND b.sort_order =
(SELECT
MIN(c.sort_order)
FROM
Accommodation_Packages AS c
WHERE
c.business_id = a.business_id)))
ORDER BY business_id

___________________

SELECT T5.*
FROM Accommodation_Packages T5 INNER JOIN
(SELECT MIN(T3.Package_ID) AS Package_ID
FROM Accommodation_Packages T3
JOIN
(SELECT
T1.business_id, MIN(T1.sort_order) AS min_sort_order
FROM
Accommodation_Packages T1
GROUP BY
T1.business_id) T2 ON T3.business_id = T2.business_id AND T3.sort_order
=
T2.min_sort_order
GROUP BY T3.Business_ID) T4 ON
T4.Package_ID =
T5.Package_ID
Cheers

Gary