Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
smh
Yak Posting Veteran
94 Posts |
Posted - 2012-12-27 : 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
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-27 : 14:53:03
|
SELECT TOP 4 *FROM (select * from yourTable where yourDate <= '20120119' )Order By yourDate DESCJimEveryday I learn something that somebody else already knew |
|
|
smh
Yak Posting Veteran
94 Posts |
Posted - 2012-12-27 : 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 descThis 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)? |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-27 : 15:38:01
|
I think you can just change it to be TOP 1, and instead of ORDER BY yourDate desc just doORDER BY yourDate ASCJimEveryday I learn something that somebody else already knew |
|
|
smh
Yak Posting Veteran
94 Posts |
Posted - 2012-12-27 : 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.Thanksto get the |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-27 : 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 statementselect top 4 * from tblpayrolldatedetail where payrollDate <= '1/16/2012'order by payrollDate descselect 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 JimJimEveryday I learn something that somebody else already knew |
|
|
smh
Yak Posting Veteran
94 Posts |
Posted - 2012-12-27 : 18:04:51
|
Yes, this works and thanks very much. I did not realize this could be done with "select top num" |
|
|
|
|
|