?
Solved

Top 2 Date Values SQL Statement

Posted on 2011-10-13
9
Medium Priority
?
303 Views
Last Modified: 2012-05-12
I'm trying to write a select query in Access whereby I select all those records having the two most recent dates. How do I write that? Something like this perhaps:

SELECT *
FROM T_MyTable
WHERE DATE In(SELECT Top(2) Date FROM T_MyTable)

0
Comment
Question by:TimHudspith
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 36960650
Yes that's fine in principle but you need an 'Order By [Date] desc' in your subquery.

Calling a field 'Date' is going to give you problems.

If you can, change it to something more meaningful (SalesDate or TransactionDate for example).

If it's too late for that then you have to enclose it in [ ] everywhere you use it.
0
 
LVL 7

Assisted Solution

by:kemi67
kemi67 earned 800 total points
ID: 36960651
You where almost true. You need to sort the date and use distinct
You need something like this
SELECT *
FROM T_MyTable
WHERE Date  In(SELECT distinct Top 2  Date  FROM  T_MyTable order by Date desc)

Open in new window

0
 
LVL 9

Expert Comment

by:pritamdutt
ID: 36960652
The query would something like

SELECT s.*
FROM T_MyTable s
WHERE DATE In(SELECT Top  2  t.Date FROM T_MyTable t ORDER BY t.Date DESC)

Open in new window



Regards
0
Independent Software Vendors: 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!

 
LVL 9

Expert Comment

by:Roman Gherman
ID: 36960884
Why not like this:

SELECT TOP 2 *
FROM T_MyTable 
ORDER BY [Date]

Open in new window

0
 
LVL 5

Expert Comment

by:eridanix
ID: 36960976
The right use is:


SELECT TOP 2 *
FROM T_MyTable
ORDER BY [Date] DESC
0
 
LVL 9

Expert Comment

by:Roman Gherman
ID: 36960997
eridanix - yes, forgot to add Desc, thanks
0
 
LVL 7

Expert Comment

by:kemi67
ID: 36961165
but SELECT TOP 2 * doesn't work  if there are two records with the same most recent date.
You need to use SELECT DISTINCT TOP 2 * in such case, if you want the two different most recent dates
0
 
LVL 5

Expert Comment

by:eridanix
ID: 36961219
kemi67: But author did not wrote, that he want two different dates, but only most recent last dates.
In other words, to select two rows from table, which are newest.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1200 total points
ID: 36961583
assuming your table name is Orders with orderdate and customerid fields


select t.*
from Orders AS t
where t.OrderDate In (select top 2 [OrderDate] from Orders t2 where t2.customerid=t.customerid order by [OrderDate] desc)
order by t.CustomerID
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

850 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