SQL/Query to retrieve top two records from a panel of data?

[Alan....@gmail.com]

Hi,

I have a panel of data organised as follows:

id1, 1990, 100
id1, 1991, 101
id1, 1992, 100
id1, 1993, 103
id2, 1990, 80
id2, 1991, 90
id2, 1992, 81
id2, 1993, 83
id3..... etc.

Is there any way to -- without re-structuring the data -- write a
query that will return the top two records based on the third column?
Like the max function but for the top two instead of top one. The
desired output would be:

id1, 1993, 103
id1, 1991, 101
id2, 1991, 90
id2, 1993, 83
id3.... etc.

Thanks in advance for your time,

Alan

[Alan....@gmail.com]

[Rich P]

Hi Alan,

This kind of query is doable - but not with Access Jet sql. Transact
sql (tsql for sql server) can handle something like this as follows:

SELECT * FROM #tmp1 t1 WHERE EXISTS
(SELECT * FROM (SELECT TOP 2 * FROM #tmp1 t2 WHERE t2.RecordId =
t1.RecordId ORDER BY val1 desc) x WHERE x.rowID = t1.rowID)

The trick here is that you need an identity column (autonum column in
Access) - which you did not show in your sample data. You still need
the column which contains id1, id2, ... I named that column RecordID in
my sample above. Then you have to Order By (Desc in your case) on the
desired column

- Note: A Top clause needs to be followed by an Order By statement
(Asc, Desc) because it does not know the Top of what?

I have not been able to get this sql statement to work in Jet sql. Tsql
supports about 90-95% of Jet sql statements/syntax, but Jet sql only
supports about 30% of Tsql syntax -- this is because Tsql has a much
broader spectrum than Jet sql since it is for server based databases.

To get this to work in Access you would need to create 2 temporary
tables and loop through your data set and store all the records for id1
in the first temp table then select the top 2 rows - store them in the
2nd temp table, then select all rows for id2 into the first temp talbe -
select the top 2 rows from that and save them in the 2nd temp table, the
loop for id3 ...

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[lyle fairfield]

With Northwinds this query

SELECT TOP 2 * FROM
(SELECT TOP 2 *
FROM [Order Details]
ORDER BY [Unit Price] DESC)

returns

ID Order ID Product Quantity Unit Price Discount Status ID Date
Allocated Purchase Order ID Inventory ID
84 58 Northwind Traders Marmalade 40 $81.00 0.00% Invoiced
134
30 31 Northwind Traders Dried Apples 10 $53.00 0.00% Invoiced
65

There are two records with a [Unit Price] of $53.00 so three records are
returned by
SELECT TOP 2 *
FROM [Order Details]
ORDER BY [Unit Price] DESC

So we select the top 2 from those three with
SELECT TOP 2 * FROM
(SELECT TOP 2 *
FROM [Order Details]
ORDER BY [Unit Price] DESC)

If you are using Access 97 there will be a problem with delimiters; one
could solve this problem with a saved query named "QueryWhatever" whose SQL
will be SELECT TOP 2 *
FROM [Order Details]
ORDER BY [Unit Price] DESC

and THE Query whose SQL will be
SELECT TOP 2 * FROM
QueryWhatever

All the preceding is predicated on the notion that I understand what you
have written, which is fifty-fifty.

Alan....@gmail.com wrote in news:f945b63d-98a7-4d6e-86fc-68785f74b70d@
8g2000hse.googlegroups.com:

[CDMAP...@fortunejames.com]

On Jul 9, 11:28 am, Rich P <rpng...@aol.com> wrote:
> Hi Alan,
>
> This kind of query is doable - but not with Access Jet sql.  Transact
> sql (tsql for sql server) can handle something like this as follows:

Perhaps the language "not doable" is a little strong :-).

tblPanel
DataField

id1, 1990, 100
id1, 1991, 101
id1, 1992, 100
id1, 1993, 103
id2, 1990, 80
id2, 1991, 90
id2, 1992, 81
id2, 1993, 83

id3, 1994, 88

qryPanel:
SELECT Left(DataField, InStr(DataField, ",") - 1) AS ForeignID,
Right(DataField, Len(DataField) - Len(ForeignID) - 2) AS Remainder,
Left(Remainder, InStr(Remainder, ",") - 1) AS Value1, Right(Remainder,
Len(Remainder) - Len(Value1) - 2) AS Value2
FROM tblPanel;

!qryPanel:
ForeignID Remainder Value1 Value2
id1 1990, 100 1990 100
id1 1991, 101 1991 101
id1 1992, 100 1992 100
id1 1993, 103 1993 103
id2 1990, 80 1990 80
id2 1991, 90 1991 90
id2 1992, 81 1992 81
id2 1993, 83 1993 83
id3 1994, 88 1994 88

qryPanel2:
SELECT ForeignID, First(qryPanel.Value1) AS Value1,
First(qryPanel.Value2) AS Value2
FROM qryPanel
WHERE Value1 = (SELECT Max(Val(A.Value1)) FROM qryPanel AS A WHERE
A.ForeignID = qryPanel.ForeignID)
GROUP BY ForeignID UNION ALL SELECT ForeignID, First(qryPanel.Value1)
AS Value1, First(qryPanel.Value2) AS Value2
FROM qryPanel
WHERE Value2 = (SELECT Max(Val(A.Value2)) FROM qryPanel AS A WHERE
A.ForeignID = qryPanel.ForeignID AND A.Value1 <> (SELECT
Max(Val(A.Value1)) FROM qryPanel AS A WHERE A.ForeignID =
qryPanel.ForeignID))
GROUP BY ForeignID
ORDER BY ForeignID, Value1 DESC;

!qryPanel2:
ForeignID Value1 Value2
id1 1993 103
id1 1991 101
id2 1993 83
id2 1991 90
id3 1994 88

This can be rolled up into a single query (for versions later than A97
the syntax is only slightly different, using regular parentheses and
no dot):

qryMaxOneTwo:
SELECT ForeignID, First(qryPanel.Value1) AS Value1,
First(qryPanel.Value2) AS Value2
FROM qryPanel
WHERE Value1 = (SELECT Max(Val(A.Value1)) FROM qryPanel AS A WHERE
A.ForeignID = qryPanel.ForeignID)
GROUP BY ForeignID UNION ALL SELECT ForeignID, First(qryPanel.Value1)
AS Value1, First(qryPanel.Value2) AS Value2
FROM qryPanel
WHERE Value2 = (SELECT Max(Val(A.Value2)) FROM qryPanel AS A WHERE
A.ForeignID = qryPanel.ForeignID AND A.Value1 <> (SELECT
Max(Val(A.Value1)) FROM qryPanel AS A WHERE A.ForeignID =
qryPanel.ForeignID))
GROUP BY ForeignID
ORDER BY ForeignID, Value1 DESC;

!qryMaxOneTwo:
ForeignID Value1 Value2
id1 1993 103
id1 1991 101
id2 1993 83
id2 1991 90
id3 1994 88

Note that I assumed no Null values exist. If they do, use the Nz
function appropriately in the SQL.

James A. Fortune
CDMAP...@FortuneJames.com

[CDMAP...@fortunejames.com]

On Jul 11, 7:03 pm, CDMAPos...@fortunejames.com wrote:

> qryMaxOneTwo:
> SELECT ForeignID, First(qryPanel.Value1) AS Value1,
> First(qryPanel.Value2) AS Value2
> FROM qryPanel
> WHERE Value1 = (SELECT Max(Val(A.Value1)) FROM qryPanel AS A WHERE
> A.ForeignID = qryPanel.ForeignID)
> GROUP BY ForeignID UNION ALL SELECT ForeignID, First(qryPanel.Value1)
> AS Value1, First(qryPanel.Value2) AS Value2
> FROM qryPanel
> WHERE Value2 = (SELECT Max(Val(A.Value2)) FROM qryPanel AS A WHERE
> A.ForeignID = qryPanel.ForeignID AND A.Value1 <> (SELECT
> Max(Val(A.Value1)) FROM qryPanel AS A WHERE A.ForeignID =
> qryPanel.ForeignID))
> GROUP BY ForeignID
> ORDER BY ForeignID, Value1 DESC;

This should be:

qryMaxOneTwo:
SELECT ForeignID, First(qryPanel.Value1) AS Value1,
First(qryPanel.Value2) AS Value2

FROM [SELECT Left(DataField, InStr(DataField, ",") - 1) AS ForeignID,

Right(DataField, Len(DataField) - Len(ForeignID) - 2) AS Remainder,
Left(Remainder, InStr(Remainder, ",") - 1) AS Value1, Right(Remainder,
Len(Remainder) - Len(Value1) - 2) AS Value2

FROM tblPanel]. qryPanel WHERE Value1 = (SELECT Max(Val(A.Value1))

FROM qryPanel AS A WHERE A.ForeignID = qryPanel.ForeignID)
GROUP BY ForeignID UNION ALL SELECT ForeignID, First(qryPanel.Value1)
AS Value1, First(qryPanel.Value2) AS Value2
FROM qryPanel
WHERE Value2 = (SELECT Max(Val(A.Value2)) FROM qryPanel AS A WHERE
A.ForeignID = qryPanel.ForeignID AND A.Value1 <> (SELECT
Max(Val(A.Value1)) FROM qryPanel AS A WHERE A.ForeignID =
qryPanel.ForeignID))
GROUP BY ForeignID
ORDER BY ForeignID, Value1 DESC;

James A. Fortune
CDMAP...@FortuneJames.com

[Rich P]

>>
.

qryMaxOneTwo:
SELECT ForeignID, First(qryPanel.Value1) AS Value1,
First(qryPanel.Value2) AS Value2
FROM qryPanel
WHERE Value1 = (SELECT Max(Val(A.Value1)) FROM qryPanel AS A WHERE
A.ForeignID = qryPanel.ForeignID)
GROUP BY ForeignID UNION ALL SELECT ForeignID, First(qryPanel.Value1)
AS Value1, First(qryPanel.Value2) AS Value2
FROM qryPanel
WHERE Value2 = (SELECT Max(Val(A.Value2)) FROM qryPanel AS A WHERE
A.ForeignID = qryPanel.ForeignID AND A.Value1 <> (SELECT
Max(Val(A.Value1)) FROM qryPanel AS A WHERE A.ForeignID

qryPanel.ForeignID))
GROUP BY ForeignID
ORDER BY ForeignID, Value1 DESC;
<<

This method does sort of preclude having to loop but still uses multiple
queries against the source data - which is equivalent to have the 2 temp
tables. Either method should work - the looping method a little bit
simpler.

[CDMAP...@fortunejames.com]

I agree. In the past I have found that minimizing the number of loops
using, say, recordsets in VBA code is often much much faster than
using SQL containing subqueries when the number of records/rows is
large. The JET SQL solution shown works with an Access backend also.

James A. Fortune
CDMAP...@FortuneJames.com