Getting records for latest 4 dates

Hi,
I would like to retrieve all the records in a table that are in the
latest 4 dates.
Each record has a date field. The latest date in the table may be today,
yesterday or last w - it varies.
I would like to be able to retrieve all records that belong to the
latest 4 dates in the table. Is it possible to do this in a stored
procedure?
Thanks,
DarenHi Daren
You may want to try something like:
CREATE TABLE #datedata ( dateval datetime, val varchar(10))
insert intO #datedata ( dateval, val )
select getdate(), 'a' as dateval
UNION ALL select getdate() -1, 'F'
UNION ALL select getdate() -2, 'b'
UNION ALL select getdate() -3, 'C'
UNION ALL select getdate() -4, 'E'
UNION ALL select getdate() -5, 'D'
SELECT * from #datedata
WHERE DATEVAL IN (
SELECT TOP 4 dateval FROM #datedata order by dateval DESC )
John
"Daren" <pearcy@.-removethis-gmail.com> wrote in message
news:42a2c8f6$0$303$cc9e4d1f@.news-text.dial.pipex.com...
> Hi,
> I would like to retrieve all the records in a table that are in the latest
> 4 dates.
> Each record has a date field. The latest date in the table may be today,
> yesterday or last w - it varies.
> I would like to be able to retrieve all records that belong to the latest
> 4 dates in the table. Is it possible to do this in a stored procedure?
> Thanks,
> Daren|||Hi John,
I don't think this does what I'm after. I'm not looking to get all
records with dates in the last 4 days. I'm looking to get records with
dates in the last 4 days in the table.
Looking at the example table below, the SQL I'm after would retrieve
record ids 3999 to 3991 inclusive.
RecordID Date Added
3999 25-May-2005
3998 25-May-2005
3997 23-May-2005
3996 21-May-2005
3995 21-May-2005
3994 21-May-2005
3993 21-May-2005
3993 21-May-2005
3992 18-May-2005
3991 18-May-2005
3990 16-May-2005
3989 16-May-2005
3988 15-May-2005
Regards,
Daren
John Bell wrote:
> Hi Daren
> You may want to try something like:
>
> CREATE TABLE #datedata ( dateval datetime, val varchar(10))
> insert intO #datedata ( dateval, val )
> select getdate(), 'a' as dateval
> UNION ALL select getdate() -1, 'F'
> UNION ALL select getdate() -2, 'b'
> UNION ALL select getdate() -3, 'C'
> UNION ALL select getdate() -4, 'E'
> UNION ALL select getdate() -5, 'D'
> SELECT * from #datedata
> WHERE DATEVAL IN (
> SELECT TOP 4 dateval FROM #datedata order by dateval DESC )

> John
> "Daren" <pearcy@.-removethis-gmail.com> wrote in message
> news:42a2c8f6$0$303$cc9e4d1f@.news-text.dial.pipex.com...
>
>
>|||This is why posting DDL and example data is important
(http://www.aspfaq.com/etiquett=ADe.asp?id=3D5006 ) because it removes
this sort of ambiguity.
CREATE TABLE MyWork ( RecordID int, [Date Added] datetime )
INSERT INTO MyWork ( RecordID, [Date Added] )
SELECT 3999, '25-May-2005'
UNION ALL SELECT 3998, '25-May-2005'
UNION ALL SELECT 3997, '23-May-2005'
UNION ALL SELECT 3996, '21-May-2005'
UNION ALL SELECT 3995, '21-May-2005'
UNION ALL SELECT 3994, '21-May-2005'
UNION ALL SELECT 3993, '21-May-2005'
UNION ALL SELECT 3993, '21-May-2005'
UNION ALL SELECT 3992, '18-May-2005'
UNION ALL SELECT 3991, '18-May-2005'
UNION ALL SELECT 3990, '16-May-2005'
UNION ALL SELECT 3989, '16-May-2005'
UNION ALL SELECT 3988, '15-May-2005'
SELECT * from MyWork
WHERE [Date Added] IN (
SELECT TOP 4 [Date Added] FROM ( SELECT DISTINCT [Date Added] FROM
MyWork ) A order by [Date Added] DESC )
This also seems to work as TOP is applied after building the result
set.
SELECT * from MyWork
WHERE [Date Added] IN (
SELECT DISTINCT TOP 4 [Date Added] FROM MyWork order by [Date Added]
DESC )=20
John|||John Bell wrote:
> This is why posting DDL and example data is important
> (http://www.aspfaq.com/etiquett_e.asp?id=5006 ) because it removes
> this sort of ambiguity.
>
Thanks for that advice, I'll try to remember it for future postings.

> This also seems to work as TOP is applied after building the result
> set.
> SELECT * from MyWork
> WHERE [Date Added] IN (
> SELECT DISTINCT TOP 4 [Date Added] FROM MyWork order by [Date Added]
> DESC )
>
D'oh! Why didn't I think of that? Thanks, much appreciated!
Daren|||SELECT * FROM tbl WHERE d in (SELECT TOP 4 d FROM tbl ORDER BY d DESC)
"Daren" wrote:

> Hi,
> I would like to retrieve all the records in a table that are in the
> latest 4 dates.
> Each record has a date field. The latest date in the table may be today,
> yesterday or last w - it varies.
> I would like to be able to retrieve all records that belong to the
> latest 4 dates in the table. Is it possible to do this in a stored
> procedure?
> Thanks,
> Daren
>|||OOPS, you might need:
SELECT * FROM tbl WHERE d in (SELECT TOP 4 d FROM (SELECT DISTINCT d FROM
tbl) a ORDER BY d DESC)
"Brian Selzer" wrote:
> SELECT * FROM tbl WHERE d in (SELECT TOP 4 d FROM tbl ORDER BY d DESC)
> "Daren" wrote:
>