Correlated Subquery with MIN()

Question

Post reply

Correlated Subquery with MIN()

knovak

Right there with Babe

Points: 779
More actions
March 5, 2010 at 4:34 pm

#139156

I have the following set of data...
RowNumber mftp_type symbol type
----------- ---------- ---------- ----------
1 CL pdsl.a clus
2 CL pdsl.a ulus
3 CL jnja.n ulus
I want the results of my query below to be...
RowNumber mftp_type symbol type
----------- ---------- ---------- ----------
1 CL pdsl.a clus
3 CL jnja.n ulus
However, the actual results I'm getting are...
RowNumber mftp_type symbol type
----------- ---------- ---------- ----------
1 CL pdsl.a clus
What is wrong with the following query that I'm not getting the expected results?
SELECT mftp_type
, symbol
, type
FROM A_typemaintest
WHERE RowNumber = (SELECT MIN(RowNumber)
FROM A_typemaintest b
WHERE b.mftp_type = mftp_type
AND b.symbol = symbol)
Thanks,
Kyle

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

If you'd run your subquery separately you'd see that it will return only one row.

Assuming RowNumber is a normal column the you simply could use the GROUP BY function.

Something like

SELECT

MIN(RowNumber) AS RowNumber

, mftp_type

, symbol

, [TYPE]

FROM cte

GROUP BY

mftp_type , symbol, [TYPE]

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

Try this :

SELECT A_typemaintest.symbol

, A_typemaintest.mftp_type

, A_typemaintest.RowNumber

, A_typemaintest.type

FROM A_typemaintest

JOIN (SELECT symbol

, mftp_type

, MIN(RowNumber) as lowestRowNumber

FROM A_typemaintest

group by symbol , mftp_type

) First_t

on First_t.mftp_type = A_typemaintest. mftp_type

and First_t.symbol = A_typemaintest.symbol

and First_t.lowestRowNumber= A_typemaintest.RowNumber

SQL = Scarcely Qualifies as a Language

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 3

lmu92 (3/5/2010)
If you'd run your subquery separately you'd see that it will return only one row.
Assuming RowNumber is a normal column the you simply could use the GROUP BY function.
Something like
SELECT
MIN(RowNumber) AS RowNumber
, mftp_type
, symbol
, [TYPE]
FROM cte
GROUP BY
mftp_type , symbol, [TYPE]

Heh... I love simple. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

knovak Right there with Babe Points: 779 More actions · Answer 4

Thanks for the responses. I figured out the query about 10 minutes after I posted here. I was missing the IN and GROUP BY clauses. The final query looks like the following...

SELECT mftp_type

, symbol

, type

FROM A_typemaintest

WHERE RowNumber IN (SELECT MIN(RowNumber)

FROM A_typemaintest b

WHERE b.mftp_type = mftp_type

AND b.symbol = symbol

GROUP BY b.mftp_type, b.symbol)

LutzM SSC Guru Points: 107049 More actions · Answer 5

knovak (3/8/2010)
Thanks for the responses. I figured out the query about 10 minutes after I posted here. I was missing the IN and GROUP BY clauses. The final query looks like the following...
SELECT mftp_type
, symbol
, type
FROM A_typemaintest
WHERE RowNumber IN (SELECT MIN(RowNumber)
FROM A_typemaintest b
WHERE b.mftp_type = mftp_type
AND b.symbol = symbol
GROUP BY b.mftp_type, b.symbol)

Why do you make it more complicated than it needs to be?

Did you try the code I posted above?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

knovak Right there with Babe Points: 779 More actions · Answer 6

I did try the code you posted and it's not returning the result set I'm looking for. If you take this set of data

RowNumber mftp_type symbol type

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

1 CL pdsl.a clus

2 CL pdsl.a ulus

3 CL jnja.n ulus

4 CL sktc.g clus

5 CL sktc.g ulus

And run that SQL, you receive the same exact result set. What I'm looking for is the following result set, which is fulfilled by the modified query I posted. The straight GROUP BY fails on the Type column, which is the differentiating factor in all this.

mftp_type symbol type

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

CL pdsl.a clus

CL jnja.n ulus

CL sktc.g clus

LutzM SSC Guru Points: 107049 More actions · Answer 7

Ooopps!! You're right. I missed that the [type] column holds different data. I didn't spot the different values clus vs. ulus.

Your code should run just fine.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 8

My suggestion would have been:

DECLARE @T

TABLE (

RowNumber BIGINT PRIMARY KEY,

mftp_type CHAR(2) NOT NULL,

symbol CHAR(6) NOT NULL,

type CHAR(4) NOT NULL

);

INSERT @T

(RowNumber, mftp_type, symbol, type)

SELECT 1, 'CL', 'pdsl.a', 'clus' UNION ALL

SELECT 2, 'CL', 'pdsl.a', 'ulus' UNION ALL

SELECT 3, 'CL', 'jnja.a', 'ulus';

SELECT T1.RowNumber,

T1.mftp_type,

T1.symbol,

T1.type

FROM @T T1

WHERE T1.RowNumber =

(

SELECT MIN(T2.RowNumber)

FROM @T T2

WHERE T2.symbol = T1.symbol

);

Note the absence of a GROUP BY in the correlated sub-query. It produces a much more efficient plan (with Segment Top - my favourite!)

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi