SQL problem concerning composite key

Last Post 04 Nov 2006 01:33 PM by SQLUSA. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Baard
New Member
New Member

--
03 Nov 2006 12:32 AM


Hi,

--- Scenario ---
A table with a composite primary key consisting of column one and two.

A query should return a row with the lowest value of column one and the largest value of column two.

The following query works, but I am seeking a better solution. I think I’ve seen a better method of solving this before, but I cannot remember it.

/Baard

--- Script ---

SET NOCOUNT ON
GO

CREATE TABLE FOO (
PK_Col1 VARCHAR(10),
PK_Col2 INT,
Data VARCHAR(10) NULL,
PRIMARY KEY (PK_Col1, PK_Col2)
)
GO

INSERT INTO FOO (PK_Col1, PK_Col2, Data) VALUES ('a', 1, 'Wrong');
INSERT INTO FOO (PK_Col1, PK_Col2, Data) VALUES ('a', 2, 'Wrong');
INSERT INTO FOO (PK_Col1, PK_Col2, Data) VALUES ('a', 3, 'Correct');
INSERT INTO FOO (PK_Col1, PK_Col2, Data) VALUES ('b', 1, 'Wrong');
INSERT INTO FOO (PK_Col1, PK_Col2, Data) VALUES ('b', 2, 'Wrong');
INSERT INTO FOO (PK_Col1, PK_Col2, Data) VALUES ('b', 3, 'Wrong');
INSERT INTO FOO (PK_Col1, PK_Col2, Data) VALUES ('b', 4, 'Wrong');
GO


/*
Query should return the row with lowest PK_Col1
and largest PK_Col2 within that result.
*/
SELECT
*
FROM
FOO F1
WHERE
F1.PK_Col1 = (
SELECT MIN(F2.PK_Col1)
FROM FOO F2
)
AND F1.PK_Col2 = (
SELECT MAX(F3.PK_Col2)
FROM FOO F3
WHERE F3.PK_Col1 = (
SELECT MIN(F4.PK_Col1)
FROM FOO F4
)
)
GO

DROP TABLE FOO
GO

mwesch
New Member
New Member

--
03 Nov 2006 02:52 AM
Here's one way to write SQL that looks shorter. Execution plan shows as half the cost.

----------------------------------------------------------

select top 1 *
from foo x
where x.pk_col1 = (select min(pk_col1) from foo)
order by x.pk_col2 desc
Baard
New Member
New Member

--
03 Nov 2006 03:40 AM
Thanks for your answer! I think that was the method I could not remember.

If this was merely a SS issue I would let it rest there :) , but the question came up through a coworker who had this type of query in a mainframe DB2 database. I don’t know if DB2 in this environment supports similar functionality to TOP 1, but the solution as such, cannot be applied without alterations.

If you (or anyone else) could think of an alternative method to solve this, without the use of TOP 1, I would appreciate it (but I guess I probably should post this in a DB2 forum instead)?

/Bård
Baard
New Member
New Member

--
03 Nov 2006 03:59 AM
Here is an attempt I tried:

SELECT
*
FROM
FOO
WHERE (PK_Col1 + CAST((10 - PK_Col2) AS VARCHAR(5))) = (
SELECT MIN(PK_Col1 + CAST((10 - PK_Col2) AS VARCHAR(5)))
FROM FOO
)

It produces the correct result, but it would probably not perform very well without function based indexes.

/Bård
mwesch
New Member
New Member

--
03 Nov 2006 01:19 PM
Here's another using standard ANSI syntax. I'm not familiar with DB2 variations, but I would expect this should work.

------------------------------------------------

select x.*
from foo x
inner join
(
select a.pk_col1, max(a.pk_col2) as pk_col2
from foo as a
inner join (select min(pk_col1) as pk_col1 from foo) as b
on a.pk_col1 = b.pk_col1
group by a.pk_col1
) as y
on x.pk_col1 = y.pk_col1
and x.pk_col2 = y.pk_col2
SQLUSA
New Member
New Member

--
04 Nov 2006 01:33 PM
True. Correlated subquery can cause performace degradation.

Kalman Toth DB, DW & BI Architect
URL: http://www.sqlusa.com
The Best SQL Server 2005 Training in the World

You are not authorized to post a reply.

Acceptable Use Policy