Nested Select?

Question

Post reply

Nested Select?

Ads-812946

SSC-Addicted

Points: 432
More actions
October 20, 2008 at 7:01 am

#199811

Hi
I have a query where I am bringing back the top 2 (ordered by date descending) records where certain keywords are present in the "Description" coulmn. I am getting the 2 records with the most recent dates but I would only like to see the 2nd record of the 2.
The code I have used to return the 2 cases is as follows;
SELECT TOP 2 * FROM PRODUCTS WHERE ID = 'A1425' AND DESC LIKE 'FLAG SET TO%' ORDER BY DATE DESC
Has anyone got any ideas on how to get the 2nd record?

Viewing 11 posts - 1 through 10 (of 10 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

This should do the trick:

SELECT TOP 1 * FROM (

SELECT TOP 2 *

FROM PRODUCTS

WHERE ID = 'A1425' AND DESC LIKE 'FLAG SET TO%'

ORDER BY DATE DESC

) d ORDER BY DATE

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ads-812946 SSC-Addicted Points: 432 More actions · Answer 2

Ads-812946

SSC-Addicted

Points: 432

October 20, 2008 at 7:48 am

#887121

Chris, that has worked a treat. Many Thanks.

Ads-812946 SSC-Addicted Points: 432 More actions · Answer 3

Hi (again)

I have modified a piece of code (suggested by Chris) that works on its own but as a subquery I am getting the following error;

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I have adapted the query as follows;

SALES.ID,

(SELECT TOP 1 PRODUCT.INFO FROM (SELECT TOP 2 * FROM PRODUCTS WHERE PRODUCTS.ID = SALES.ID AND DESC LIKE 'FLAG SET TO%' ORDER BY DATE DESC) d ORDER BY DATE)

FROM SALES

Any ideas on how I can combat this problem or use "EXISTS"?

Thanks

Garadin One Orange Chip Points: 29613 More actions · Answer 4

Is this the whole query? If not, please post the rest. At the time of your original question, you didn't specify it was going to be a subquery. There may now be a better solution than a correlated subquery.

In the meantime, Try this:

S.ID,

(SELECT TOP 1 D.INFO FROM (SELECT TOP 2 * FROM PRODUCTS WHERE PRODUCTS.ID = S.ID AND [DESC] LIKE 'FLAG SET TO%' ORDER BY [DATE] DESC) D ORDER BY [DATE])

FROM SALES S

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Ads-812946 SSC-Addicted Points: 432 More actions · Answer 5

Hi Seth

The complete query is as follows (apologies);

SELECT

S.ID,

(SELECT TOP 1 D.INFO FROM (SELECT TOP 2 * FROM PRODUCTS WHERE PRODUCTS.ID = S.ID AND [DESC] LIKE 'FLAG SET TO%' ORDER BY [DATE] DESC) D ORDER BY [DATE])

FROM SALES S

WHERE

S.ID = 'A1425'

I have tried this but it returned a NULL value. As per Chris's query I know that for this ID (A1425) there should definitely be a narrative i.e. D.INFO.

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Not sure, as you didn't provide the DDL for the tables or any sample data to test with, but does this work?

;with ProductList (

RowNum,

ProductID,

Description,

Info,

OrderDate

) as (

select

row_number() over (partition by ID, order by ID, [DATE] desc) as RowNum,

ID,

[DESC],

INFO,

[DATE]

from

PRODUCTS

where

[DESC] like 'FLAG SET TO%'

)

select

S.ID,

pl.Description

from

SALES S

inner join ProductList pl

on (S.ID = pl.ProductID

and pl.RowNum = 2)

where

S.ID = 'A1425';

😎

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

This should do it...

SELECT TOP 1 d.[ID], d.INFO

FROM (

SELECT TOP 2 s.[ID], p.INFO, p.[DATE]

FROM PRODUCTS p

INNER JOIN SALES s ON s.[ID] = p.[ID]

WHERE p.[DESC] LIKE 'FLAG SET TO%'

AND s.ID = 'A1425'

ORDER BY p.[DATE] DESC

) d ORDER BY d.[DATE]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ads-812946 SSC-Addicted Points: 432 More actions · Answer 8

I think I may have confused the matter in my last post which was;

SELECT

S.ID,

(SELECT TOP 1 D.INFO FROM (SELECT TOP 2 * FROM PRODUCTS WHERE PRODUCTS.ID = S.ID AND [DESC] LIKE 'FLAG SET TO%' ORDER BY [DATE] DESC) D ORDER BY [DATE])

FROM SALES S

WHERE

S.ID = 'A1425'

In Chris's last post he has supplied a query that does the same thing as the query he sent initially.

I entered the ID "A1425" for ease of use so that I didn't get lots of records brought back.

So essentially this is the code I would use (or would like to use).

SELECT

S.ID,

(SELECT TOP 1 D.INFO FROM (SELECT TOP 2 * FROM PRODUCTS WHERE PRODUCTS.ID = S.ID AND [DESC] LIKE 'FLAG SET TO%' ORDER BY [DATE] DESC) D ORDER BY [DATE])

FROM SALES S

WHERE

Garadin One Orange Chip Points: 29613 More actions · Answer 9

Have you tried Lynn's solution?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Ads-812946 SSC-Addicted Points: 432 More actions · Answer 10

Hi

I have got the initial piece of code Chris posted to work.

Thanks for the help.