Last 7 records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AlanW - 02 Oct 2007 10:21 GMT

Could someone please show me how to extract the last 7 records from the code
below

SELECT Table1.A, Table1.B
FROM Table1
ORDER BY Table1.A DESC;

Thanks

Reply to this Message

Maurice - 02 Oct 2007 11:11 GMT

Maybe this will do the trick:

SELECT Top 7 Table1.A, Table1.B
FROM Table1
ORDER BY Table1.A DESC;

hth

Signature

Maurice Ausum

> Could someone please show me how to extract the last 7 records from the code
> below
[quoted text clipped - 4 lines]
>
> Thanks

Reply to this Message

RDunlap - 02 Oct 2007 14:00 GMT

Since he wanted the LAST 7 records, shouldn't you change the Order By to
Ascending?

> Maybe this will do the trick:
>
[quoted text clipped - 12 lines]
> >
> > Thanks

Reply to this Message

Rick Brandt - 02 Oct 2007 14:32 GMT

> Since he wanted the LAST 7 records, shouldn't you change the Order By
> to Ascending?

That would totally depend on what value is in the field "A" and how that
relates to "Last" and "First". Generically one would expect to sort in
descending order to get "Last" and ascending order to get "First", but
again, it would depend on the data.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Klatuu - 02 Oct 2007 14:48 GMT

Also, you may not get exactly 7 records. If there can be duplicate values,
it will return all the records that match the top 7 values. For example

333
252
252
187
155
155
155
155
105
99
99
87
62
62
54
32
32

Signature

Dave Hargis, Microsoft Access MVP

> > Since he wanted the LAST 7 records, shouldn't you change the Order By
> > to Ascending?
[quoted text clipped - 3 lines]
> descending order to get "Last" and ascending order to get "First", but
> again, it would depend on the data.

Reply to this Message

Rick Brandt - 02 Oct 2007 16:10 GMT

> Also, you may not get exactly 7 records. If there can be duplicate
> values, it will return all the records that match the top 7 values.
[quoted text clipped - 17 lines]
> 32
> 32

Not quite right. You get more than 7 records from "Top 7..." only if there
are ties on the 7th value. You don't get all records matching the top 7
unique values as you have listed there. You would get...

333
252
252
187
155
155
155
155

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Klatuu - 02 Oct 2007 16:22 GMT

Rick,
You may be right, but I seem to remember having to resolve this issue once
before and as I recall, I got something more like what I posted. Now I
actually have to go do some work to test it.

Signature

Dave Hargis, Microsoft Access MVP

> > Also, you may not get exactly 7 records. If there can be duplicate
> > values, it will return all the records that match the top 7 values.
[quoted text clipped - 30 lines]
> 155
> 155

Reply to this Message

BeWyched - 02 Oct 2007 19:36 GMT

SELECT Table1.A, Table1.B
FROM Table1
GROUP BY Table1.A
ORDER BY Table1.A DESC;

will give the top 7 unique entries.

BW

> Rick,
> You may be right, but I seem to remember having to resolve this issue once
[quoted text clipped - 35 lines]
> > 155
> > 155

Reply to this Message

Maurice - 02 Oct 2007 20:38 GMT

Hold on, and what if you have 200 records which are all unique where do you
get the 7 in your statement?

Signature

Maurice Ausum

> SELECT Table1.A, Table1.B
> FROM Table1
[quoted text clipped - 44 lines]
> > > 155
> > > 155

Reply to this Message

BeWyched - 02 Oct 2007 22:40 GMT

Sorry - forgot a bit!

Should read:

SELECT Top 7 Table1.A, Table1.B
FROM Table1
GROUP BY Table1.A
ORDER BY Table1.A DESC;

> Hold on, and what if you have 200 records which are all unique where do you
> get the 7 in your statement?
[quoted text clipped - 47 lines]
> > > > 155
> > > > 155

Reply to this Message