Improve company productivity with a Business Account.Sign Up

x
?
Solved

Select Query

Posted on 2011-03-12
5
Medium Priority
?
356 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 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
To export Lotus Notes to Outlook PST or Exchange and Domino Server files to Exchange Server or PST files with ease, go for Kernel for Lotus Notes to Outlook conversion tool. Through the video, you can watch the conversion process. A common user with…

595 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