RE: SQL question

From: Leffler, Jonathan <jleffler_at_visa.com>
Date: 1998/05/11
Message-ID: <6j7dbq$5fa$1_at_news.xmission.com>#1/1

---

Guillermo Labatte (labatteg_at_frcu.utn.edu.ar) wrote:
>I'm using online 5.07 and I have a little problem here. I need to submit
 a
>query like this:
 

>select * from tabA
>where
>tabA.colX||tabA.colY=(select max(tabB.colX||tabB.coly)
> from tabB where ....)

We need an explanation of what you are trying to do, rather than a sample of what doesn't work. Since you didn't give such an explanation, you're going to have to put up with my explanation of an answer to my guess as to what you're trying to do...

You have a table TabB with integer columns ColX and ColY. You have a second table, TabA with similar columns. You need to find the entry or entries in TabA which correspond to the entry in TabB with the maximum value in ColX (call it Xmax), and within the set of rows with ColX = Xmax, the one with the largest value of ColY.

The sub-select could be written:

	SELECT ColX, MAX(ColY)
		FROM TabB
		WHERE ColX = (SELECT MAX(ColX) FROM TabB)
		GROUP BY ColX;

Then you are left with the problem of a multi-part join with a sub-select, which you'd like to be able to write as:

• Non-working code SELECT * FROM TabA WHERE (ColX, Coly) = (SELECT ColX, MAX(ColY) FROM TabB WHERE ColX = (SELECT MAX(ColX) FROM TabB) GROUP BY ColX )

        Since that isn't valid SQL (as far as I know), we have to rewrite it as:

		SELECT A.*
			FROM TabA A
			WHERE A.ColX = (SELECT MAX(ColX) FROM TabB)
			      AND A.ColY = (SELECT MAX(ColY) FROM TabB

WHERE ColX = (SELECT MAX(ColX) FROM TabB));

        A transcription of this query produced the correct answer (1,2) on a minimal pair of tables with the entries (1,1), (1,2), (0,3) in them. I didn't mention anything about efficiency. I've also omitted the 'WHERE ...' part of your query; you'd need to reintroduce it at the appropriate points in the solution - there are three such points, I think; one after each 'FROM TabB' clause.

>Since colX and colY are integers it doesn't work quite well.
>Is there a function that you can apply to those integers to pad them with
>zeroes?

No.

>Sorry but I wouldn't ask to the list and I would read the manual if I had
 a
>5.07 version manual. It seems like I have a 5.07 Online version and a
 5.00
>version manual.

The 5.0 manual plus the 5.01 triggers supplement are the correct manuals for 5.07.

>If such a function does not exist. How could I rewrite that SQL
 statement?
>I've tried
 

>select * from tabA
>where
> tabA.colX=(select max(tabB.colX) where ...) and
> tabA.colY=(select max(tabB.colY) where tabB.colX=tabA.colX and ...)
 

>but it didn't work.
Received on Mon May 11 1998 - 00:00:00 CEST