[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Select Query

Posted on 2011-03-12
5
Medium Priority
?
354 Views
Last Modified: 2012-05-11
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
Comment
Question by:stathisx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:McOz
ID: 35116214
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
 
LVL 1

Author Comment

by:stathisx
ID: 35116281
Thanks for response ,
i get syntax error in ORDER BY clause ..
0
 
LVL 1

Author Comment

by:stathisx
ID: 35116326
i forgot to mention that the query is built in access 2010 .
0
 
LVL 9

Accepted Solution

by:
McOz earned 1000 total points
ID: 35127628
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
 
LVL 1

Author Comment

by:stathisx
ID: 35129655
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question