Need help with SQL query

[regul8or]

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

[jefftyzzer]

On Feb 19, 10:18 am, "regul8or" <[e-mail]:

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

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

[Tonkuma]

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

[regul8or]

Thanks guys, that's great!

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

--
Michael

[Brian Tkatch]

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

>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.

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.