April 25, 2006 at 2:22 am
example .......
There are two tables
one is called oc and the second table is called issue.
oc.oc_id issue.issue_id issue.issue_date "next_row_date"
10 1234 01\01\2006 10\01\2006
10 1237 10\01\2006 04\02\2006
10 1239 04\02\2006
25 2461 15\01\2006 25\02\2006
25 2462 25\02\2006 01\04\2006
25 2463 01\04\2006
For the first three columns my sql is ....
select oc.oc_id, issue_id, issue_date
from oc inner join issue on oc.oc_id = issue.oc_id order by oc.oc_id,issu.issue_id
It's the 4th column data set that I want but do not know how to get it ?? - is there a next function??
When the oc_oc_id changes I do not need the next issue.issue_date in column "next_row_date"
Help!!
Phil
April 25, 2006 at 3:31 am
Data as posted:
create table oc (oc_id int)
create table issue (oc_id int, issue_id int, issue_date datetime)
insert into oc values (10)
insert into oc values (25)
insert into issue(oc_id, issue_id, issue_date) values (10, 1234, '20060101')
insert into issue(oc_id, issue_id, issue_date) values (10, 1237, '20060110')
insert into issue(oc_id, issue_id, issue_date) values (10, 1239, '20060204')
insert into issue(oc_id, issue_id, issue_date) values (25, 2461, '20060115')
insert into issue(oc_id, issue_id, issue_date) values (25, 2462, '20060225')
insert into issue(oc_id, issue_id, issue_date) values (25, 2463, '20060401')
On the given data following query does the trick:
select oc.oc_id, i.issue_id, i.issue_date, MIN(i2.issue_date) as next_date
from oc
inner join issue i on oc.oc_id = i.oc_id
LEFT JOIN issue i2 ON i.oc_id = i2.oc_id AND i2.issue_date > i.issue_date
GROUP BY oc.oc_id, i.issue_id, i.issue_date
order by oc.oc_id,i.issue_id
DROP TABLE oc
DROP TABLE issue
HTH, Vladan
PS: I took "next row date" means "next higher date for the same oc_id". Be warned that there is no such thing as next row in SQL and should you change ordering, you'll have to rewrite the query if "next row" means something different.
April 25, 2006 at 3:48 am
Thanks for the reply - when run this happens - see row 2 - the date returned in column 4 is incorrect, it should read "2008-02-01 00:00:00.000 ". Perhaps I did not explain myself very well - so long as the next oc.oc_id is the same (column1) then, for example, row 2 column 4 would return the date from column 3 row 3.
5 167 2007-11-01 00:00:00.000 2007-12-01 00:00:00.000
5 168 2007-12-01 00:00:00.000 2008-01-01 00:00:00.000
5 2882 2008-02-01 00:00:00.000 2008-03-01 00:00:00.000
5 2883 2008-03-01 00:00:00.000 2008-04-01 00:00:00.000
Column one oc.oc_id is the same and it's where there can be a jump in the issue_id that's causing the problem.
Regards
Phil
April 25, 2006 at 4:04 am
Hi Phil (and Vladan),
Vladan's solution looks good to me (I actually did it the same way and then saw that he had beat me to it!).
I'm not sure the result makes sense, given the query. Where is '2008-01-01' coming from? Can you figure that out?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 25, 2006 at 4:10 am
Only now I realized that I should have taken the issue_id (not date) to find "next" row - according to ORDER BY clause. I supposed that the issue_id for higher issue_date will always be greater - which is not the case, as it seems.
Is there a row that has oc_id = 5 and issue_date = '20080101'? What issue_id does it have?
April 25, 2006 at 4:18 am
Yes - the issue_id is 6758
April 25, 2006 at 4:25 am
OK, that's precisely it. I warned that it only works on posted data... from posted data it looked like the issue id's increase with time.
Try this one:
select oc.oc_id, i.issue_id, i.issue_date,
(SELECT TOP 1 issue_date FROM issue WHERE oc_id = oc.oc_id AND issue_id > i.issue_id ORDER BY issue_id) as next_date
from oc
inner join issue i on oc.oc_id = i.oc_id
order by oc.oc_id,i.issue_id
April 25, 2006 at 4:32 am
Spot on ....... Vladan, thanks for your help.
Regards
Phil
April 25, 2006 at 4:44 am
When I try to create a view with your sql query the following error appears ....
Server: Msg 1033, Level 15, State 1, Procedure fsd, Line 6
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
Is there a way round this??
Sorry to be a pain
Phil
April 25, 2006 at 5:28 am
You are not 🙂 no problem.
As the message says, you can not create ordered view. Ordering has to be done when selecting from the view. You have to leave out the last line of code:
CREATE VIEW my_view
AS
select oc.oc_id, i.issue_id, i.issue_date,
(SELECT TOP 1 issue_date FROM issue WHERE oc_id = oc.oc_id AND issue_id > i.issue_id ORDER BY issue_id) as next_date
from oc
inner join issue i on oc.oc_id = i.oc_id
Then, when you are selecting data from this view, just specify
SELECT <column list>
FROM my_view
ORDER BY oc_id, issue_id
The other GROUP BY shouldn't be a problem, since it is in a subselect and includes TOP 1.
April 25, 2006 at 5:32 am
Thanks again - Phil
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply