Need help with SQL query

I need a help with the following problem:

There's 2 tables tbla(primary, foreign, data) and tblb(primary, order)
with data like this:

tbla
'company 1', 1000, 'New York'
'company 1', 1001, 'Boston'
'company 2', 1000, 'Atlanta'
'company 2', 1002, 'Los Angeles'
'company 3', 1001, 'Seattle'
'company 3', 1002, 'Orlando'
'company 4', 1002, 'Denver'

tblb
1000, 97
1001, 98

Resulting table should have only one of each company WHERE
tbla.foreign = tblb.primary ORDER BY tblb.order -- like this:
'company 1', 1000, 'New York', 97
'company 2', 1000, 'Atlanta', 97
'company 3', 1001, 'Seattle', 98

I desperately need any advice.

--
Michael

re: Need help with SQL query

On Feb 19, 10:18 am, "regul8or" <regul...@gmail.comwrote:Hi, Michael,

Give this a try:

WITH
TBLA
(CONAME, PKEY, CITY)
AS
(VALUES
('company 1', 1000, 'New York'),
('company 1', 1001, 'Boston'),
('company 2', 1000, 'Atlanta'),
('company 2', 1002, 'Los Angeles'),
('company 3', 1001, 'Seattle'),
('company 3', 1002, 'Orlando'),
('company 4', 1002, 'Denver')),
TBLB
(FKEY, QTY)
AS
(VALUES
(1000, 97),
(1001, 98))
SELECT
CONAME,
PKEY,
CITY,
QTY
FROM
(
SELECT
A.CONAME,
A.PKEY,
A.CITY,
B.QTY,
ROWNUMBER() OVER (PARTITION BY A.CONAME) RN
FROM
TBLA A
JOIN
TBLB B
ON
B.FKEY = A.PKEY
) ILV
WHERE
ILV.RN = 1;

Note that you will not need the WITH TBLA VALUES...TBLB VALUES stuff--
I used those CTE's since I don't have access to your tables :-)

HTH,

--Jeff

re: Need help with SQL query

SELECT primary
, foreign
, data
, order
FROM (SELECT a.*, b.order
, ROWNUMBER() OVER(PARTITON BY a.primary
ORDER BY b.order) rn
FROM tbla a
INNER JOIN
tblb b
ON a.foreign = b.primary
) R
WHERE rn = 1

re: Need help with SQL query

Thanks guys, that's great!

(Leaving to blow the dust from Ye Olde SQL Book of Magic)

--
Michael

re: Need help with SQL query

On 19 Feb 2007 10:18:15 -0800, "regul8or" <regul8or@gmail.comwrote:

Just thought i'd join the party.

SELECT
Primary,
Foreign,
Data,
Order
FROM
(
SELECT
a1.Primary Primary,
a1.Foreign Foreign,
a1.Data Data,
b1.Order Order,
(
SELECT
MIN(Order)
FROM
SESSION.tbla a2,
SESSION.tblb b2
WHERE
b2.Primary = a2.Foreign
AND a2.Primary = a1.Primary
) The_Min
FROM
SESSION.tbla a1,
SESSION.tblb b1
WHERE
a1.Foreign = b1.Primary
) c
WHERE
Order = The_Min
ORDER BY
Order


B.