Solved Using SQL to select only 2nd record (1 Viewer)

[jazsriel]

I know there is a way in SQL to SELECT TOP 1, and if you want only the first 2 then it is SELECT TOP 2, etc...

So here is what I want to know, is there a way using SQL to select only the 2nd record instead?

 

[arnelgp]

SELECT TOP 1 * FROM (SELECT T.* FROM (SELECT TOP 2 * FROM YourTable) AS T ORDER BY T.ID DESC)

 

[jazsriel]

I am trying to see how this works in SQL, but when I try to save it gives me an error on this part...

SELECT TOP 1 FROM ((SELECT * FROM (SELECT TOP 2 * FROM yourTable) ORDER BY [AutoNumber] DESC))

It says "The SELECT statement includes a reserved word or an argument name that is misspelled or missing or the punctuation is incorrect.

 

[arnelgp]

i have edited my post, see the change

 

[jazsriel]

I see the editing. I am trying to figure this out but now it just gives me a syntax error. It says "Syntax error in FROM clause."

I am trying to go to from
SELECT TOP 2 tblRecap.*,

SELECT TOP 1 * FROM (SELECT T.* FROM (SELECT TOP 2 * FROM tblRecap) AS T ORDER BY T.ID DESC)
or is it
SELECT TOP 1 * FROM (SELECT T.* FROM (SELECT TOP 2 * FROM tblRecap) AS T ORDER BY T.ID DESC).*,
or
SELECT TOP 1 * FROM (SELECT T.* FROM (SELECT TOP 2 * FROM tblRecap.*) AS T ORDER BY T.ID DESC)

I am not familiar with SQL at all. So I am certain it is something I am doing wrong, but all 3 of these variations give me the same error.

 

[ebs17]

The suggestion above is incomplete. The use of TOP only makes sense if it is used in conjunction with an associated sorting. Every TOP needs to be sorted, otherwise the door is open to random.

Another option:

SELECT TOP 1
   *
FROM
   tblRecap
WHERE
   ID <
      (
         SELECT
            MAX(ID)
         FROM
            tblRecap
      )
ORDER BY
   ID DESC

 

[arnelgp]

the query designer change it to:

SELECT TOP 1 *
FROM (SELECT T.* FROM (SELECT TOP 2 * FROM tblRecap) AS T ORDER BY T.ID DESC) AS [%$##@_Alias];

 

[arnelgp]

Another option:

if there are 100 records, then what you will be getting is the 99 record and not the 2nd record.
on other thought, just tested your SQL, it will give you the First record.

 

[jazsriel]

I am going to upload a DB here shortly, just removing the things that I am unable to share.

 

[arnelgp]

here is the demo.

 

[jazsriel]

Well, what I need is to select the 2nd month with complete date range enabled. give me a minute I will upload, but I see what the Demo is doing.

 

[ebs17]

I added the sorting in the suggestion above. This was missing, contrary to my literal statement.

 

[jazsriel]

Okay here is the DB i was trying to upload. Sorry it took a few minutes there is a lot of information that had to be removed (privacy reasons).

Anyway, I know I probably do not do the naming correctly as some people have told me. But it works for what I need. Now if you click on the button the form it pulls up sheets by each month, January, February, etc.... From the most recent to the oldest. Now I just need to know how to pull up only the 2nd entire month. In this case it would be January 2024. Next month it will need February 2024, etc. Each month it changes to the previous months report the users would need. I se how the demo and the examples provided are working but not sure if they would work for this.

 

[arnelgp]

just to clarify today's month is February, so you need January data?

 

[jazsriel]

Yes that is correct.

 

[arnelgp]

i cannot test your db, i dont have acrobat.
but i change the query, so see the criteria on the last column of the query.

 

[jazsriel]

I do see it. I was playing around with it a bit it seems to work flawlessly. Better than what I was trying to accomplish before. I am about to leave from work in the next few minutes, so when I am back after the weekend I will integrate it into my live DB. I will say this is tentatively solved. Once I am back at work next week I will mark it as solved once I have integrated it.

But let me say thank you. When it comes to SQL I am completely lost.

 

[ebs17]

Use a filter => OpenReport with WHERE condition

[Date] >= DateSerial(Year(Date()), Month(Date()) - 1, 1)
AND [Date] < DateSerial(Year(Date()), Month(Date()), 1)

I see your query as a catastrophe, as an overall impression. Confusing, slow.
Analyzing that would be too tiring for me.

 

[ebs17]

When it comes to SQL I am completely lost.

As a learning note, index usage is very helpful for query execution performance.

' arnelgp
WHERE Format([Date],"mm/yyyy") = Format(DateAdd("m", -1, Date()),"mm/yyyy")

'ebs17
WHERE [Date] >= DateSerial(Year(Date()), Month(Date()) - 1, 1)
  AND [Date] < DateSerial(Year(Date()), Month(Date()), 1)

With @arnelgp's suggestion, the existing index on [Date] cannot be used because a calculation is made on the table field. The second suggestion is designed for index use.

The second suggestion is also more variable because other time periods can also be easily represented by changing the time period boundaries.

 

[ebs17]

Back to TOP 2

the query designer change it to:

SELECT TOP 1 *
FROM (SELECT T.* FROM (SELECT TOP 2 * FROM tblRecap) AS T ORDER BY T.ID DESC) AS [%$##@_Alias];

A query is executed from the inside out. So the first step is

SELECT TOP 2 * FROM tblRecap

Without their own sorting, the records in a table are to be viewed as an unordered set of numbers and therefore as random.

In the example of the database above, the primary key (ID) was arbitrarily set as the criterion for the order. A table is actually physically sorted by primary key after compression. As long as the query is as simple as shown and no other actions such as JOINs and/or groupings are included that can significantly change the order, the given order can actually be used without any sorting of your own. But you should be aware that this is a special case with many ifs and therefore cannot be accepted across the board.