Sub query question (1 Viewer)

Steve C · Oct 15, 2017

May I Please ask for your help with this?

From tblGrid I want the record with the top FieldX AND the top FieldY value.

As two separate queries would look like these:

SELECT TOP 1 tblGrid.GridName, tblGrid.FieldX
FROM tblGrid
WHERE tblGrid.FieldX <532917
ORDER BY tblGrid.FieldX DESC;

SELECT TOP 1 tblGrid.GridName, tblGrid.FieldY
FROM tblGrid
WHERE tblGrid.FieldY <185195
ORDER BY tblGrid.FieldY DESC;

How can I have the second query as a subquery of the first?

Thank you for your help

jdraw · Oct 16, 2017

Not sure exactly what you need but this might be a starting place

Code:

SELECT Max(fieldX) AS MaxOfX, Max(FieldY) AS MaxOfY
FROM tblGrid;

Galaxiom · Oct 16, 2017

There are several ways but it really depends on how you want the data to be presented. The results could be one record with four fields or two records with three fields.

One alternative with the four fields would be:

Code:

SELECT qA.GridName AS GridNameX,, qA.FieldX, qB.GridName AS GridNameY, qB.FieldY
 
FROM
 
(
SELECT TOP 1 tblGrid.GridName, tblGrid.FieldX
FROM tblGrid
WHERE tblGrid.FieldX <532917
ORDER BY tblGrid.FieldX DESC
) AS qA
,
(
SELECT TOP 1 tblGrid.GridName, tblGrid.FieldY
FROM tblGrid
WHERE tblGrid.FieldY <185195
ORDER BY tblGrid.FieldY DESC
) AS qB

arnelgp · Oct 16, 2017

SELECT DISTINCT tblGrid.GridName, (SELECT TOP 1 T1.FieldX FROM tblGrid AS T1 WHERE T1.GridName=tblGrid.GridName AND T1.FieldX<532917 ORDER BY T1.FieldX DESC) AS FieldX,(SELECT TOP 1 T2.FieldY FROM tblGrid AS T2 WHERE T2.GridName=tblGrid.GridName AND T2.FieldY<185195 ORDER BY T2.FieldY DESC) AS FieldY FROM tblGrid

Steve C · Oct 17, 2017

Thank You jdraw
Thank You Galaxiom
Thank you arnelgp

Fantastic and brilliant and so helpful - all

Apologies for not getting back here to thank you sooner. It took me ages to understand your answers and apply them - now my query is perfect.

Sub query question (1 Viewer)

Steve C

Registered User.

jdraw

Super Moderator

Galaxiom

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

Steve C

Registered User.

Similar threads

Users who are viewing this thread