Select Query

Hello experts ,
i have a query that returns me top 2 records from a table ordered by descending order for date and time fields.I want to select only the second row .How can this be done ?
SELECT TOP 2 ER_RL_GE_LOG.ID_GE, ER_RL_GE_LOG.LOGIN_DATE, ER_RL_GE_LOG.LOGIN_TIME
FROM ER_RL_GE_LOG
ORDER BY ER_RL_GE_LOG.LOGIN_DATE DESC , ER_RL_GE_LOG.LOGIN_TIME DESC;

Open in new window

LVL 1
stathisxAsked:
Who is Participating?
 
McOzCommented:
Sorry stathisx, you are right -- LIMIT is only supported in MySQL.

For SQL, the closest alternative is TOP like you have, so you need to use 2 steps to get ONLY the second record.
I.E get the top 1 of the top 2 sorted the other way. Something like this:
SELECT TOP 1 * 
FROM (SELECT TOP 2 ER_RL_GE_LOG.ID_GE, ER_RL_GE_LOG.LOGIN_DATE, ER_RL_GE_LOG.LOGIN_TIME FROM ER_RL_GE_LOG ORDER BY ER_RL_GE_LOG.LOGIN_DATE, ER_RL_GE_LOG.LOGIN_TIME DESC) ORDER BY ER_RL_GE_LOG.LOGIN_DATE, ER_RL_GE_LOG.LOGIN_TIME ASC;

Open in new window


Good luck!
0
 
McOzCommented:
You could use the LIMIT command, like this:
SELECT ER_RL_GE_LOG.ID_GE, ER_RL_GE_LOG.LOGIN_DATE, ER_RL_GE_LOG.LOGIN_TIME 
FROM ER_RL_GE_LOG 
ORDER BY ER_RL_GE_LOG.LOGIN_DATE DESC , ER_RL_GE_LOG.LOGIN_TIME DESC LIMIT 1,1;

Open in new window


See this link for details:
http://www.plus2net.com/sql_tutorial/second-highest.php

Cheers
0
 
stathisxAuthor Commented:
Thanks for response ,
i get syntax error in ORDER BY clause ..
0
 
stathisxAuthor Commented:
i forgot to mention that the query is built in access 2010 .
0
 
stathisxAuthor Commented:
i just changed in the last order by to include asc in both .LOGIN_DATE and .LOGIN TIME and it worked perfectly.
Thank you for assistance!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.