Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

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

0
stathisx
Asked:
stathisx
  • 3
  • 2
1 Solution
 
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
 
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now