| Author |
 Topic  |
|
|
smh
Yak Posting Veteran
USA
82 Posts |
 Posted - 12/27/2012 : 14:41:38
|
a row of a table is selected from a grid based upon a date.
I need a query to select that row and the rows with the 4 dates prior to the selected date. For example the date selected is 1/19/12, the 4 prior dates could be 11/1/11, 11/12/11, 12/3/11, 12/29/11.
I get the id and the date of the selected row from the grid so returning that record is no problem. But I cannot depend upon the identity ID's not having gaps plus the ordering of the table to get the proper dates would preclude using the ID's anyway.
In a way this is like returning the previous 'n' rows before and including row 'r' but in this case it is based upon the date not the id.
Any ideas greatly appreciated. |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/27/2012 : 14:53:03
|
SELECT TOP 4 *
FROM
(select * from yourTable where yourDate <= '20120119'
)
Order By yourDate DESC
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
smh
Yak Posting Veteran
USA
82 Posts |
Posted - 12/27/2012 : 15:26:45
|
Hi:
Thanks, I did not realize one could use top n based upon a selection. I did receive an error with your version so I tried:
select top 4 * from yourtable where yourdate <= '20120119'
order by yourdate desc
This seemed to work ok but maybe only in the sample I used.
------
Here is another question on this question. How can I return only the first ID of the list (the ID of the record with the furthest date from the selected one)? |
Edited by - smh on 12/27/2012 15:30:18 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/27/2012 : 15:38:01
|
I think you can just change it to be TOP 1, and instead of ORDER BY yourDate desc just do
ORDER BY yourDate ASC
Jim
Everyday I learn something that somebody else already knew |
|
|
|
smh
Yak Posting Veteran
USA
82 Posts |
Posted - 12/27/2012 : 16:00:10
|
Hi:
Its really like:
select top 1 * from (select top 4 * from tblpayrolldatedetail where payrollDate <= '1/16/2012'
order by payrollDate desc) order by payrolldate
in order to get the 11/1/11 date but this does not work as I get an error.
I could put the 4 records from the initial query into a #temp table and then select the top 1, but would be nice to do it in one query.
Thanks
to get the |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/27/2012 : 17:26:42
|
Nope, what you did is the right logic, just a syntax error. You have to name the set you created with this statement
select top 4 * from tblpayrolldatedetail where payrollDate <= '1/16/2012'
order by payrollDate desc
select top 1 *
from (select top 4 * from tblpayrolldatedetail where payrollDate <= '1/16/2012'
order by payrollDate desc) as NewSetIjustCreated-- you could just name it "a" or anything else you wanted
order by payrolldate
Jim
Jim
Everyday I learn something that somebody else already knew |
|
|
|
smh
Yak Posting Veteran
USA
82 Posts |
Posted - 12/27/2012 : 18:04:51
|
| Yes, this works and thanks very much. I did not realize this could be done with "select top num" |
|
|
| |
Topic |
|
select problem with dates
