Solved

Select Query

Posted on 2011-03-12
5
348 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
  • 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 250 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

832 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