New answers tagged

0

What about this solution? create table FILM ( film_id NUMBER, scene_id NUMBER, value_id NUMBER ) insert into FILM ([film_id],[scene_id],[value_id]) select 0 , 0 , 7 union all select 0 , 1 , 1 union all select 0 , 1 , 0 union all select 1 , 2 , 8 union all select 1 , 2 ...


0

Assuming that you are using reasonably recent versions of SQL Server, MySQL, and PostreSQL, they should all support analytic functions (some will call them window functions) which are relatively recent additions to the SQL standard. If so, something like this should work SELECT * FROM ( SELECT customer_number, part_number, ...


0

The previous 5 matches can easily be calculated using a running total: select id, team_1_id, team2_id, match_date, sum(case when score_team1 = score_team2 then 1 when score_team1 > score_team2 then 3 else 0 end) over (order by match_date rows between 4 preceding and current rows) as ...


0

I made a rather complicate answer with cte, should work but quite long Method: use row number for getting the team play order (overall, home/away) and sum up See if anyone can have more simple answer for that WITH cte (id, match_date, team1_id, team2_id, score1, score2, team1_order, team2_order, home_order, away_order) as ( select t.id, match_date, ...


1

Another way to solve this is to think of it as a variation of the gaps and islands pattern: SELECT *, diff = (N.rn1 - N.rn2) FROM ( SELECT *, rn1 = ROW_NUMBER() OVER ( PARTITION BY T.scanCode ORDER BY T.dates), rn2 = ROW_NUMBER() OVER ( PARTITION BY T.scanCode ORDER BY T.flag, T....


1

SQL Server 2008R2 is pretty limited in its windowing functions, no lag/lead functionality, and ORDER BY can only be used with ROW_NUMBER(). So the usual techniques to solve this are not readily available. I'm going to assume your primary key is (ScanCode,Dates), if it isn't this won't return consistent results. SELECT curr.ScanCode ,curr.Dates ,curr....


Top 50 recent answers are included