» Obtain maximum row_number inside a cross apply
Obtain maximum row_number inside a cross apply
|April 10, 2013
Posted by forumadmin
I am having trouble in calculating the maximum of a row_number in my sql case.
I will explain it directly on the SQL Fiddle example, as I think it will be faster to understand: SQL Fiddle
- Columns ‘OrderNumber’, ‘HourMinute’ and ‘Code’ are just to represent my table and hence, should not be relevant for coding purposes
- Column ‘DateOnly’ contains the dates
- Column ‘Phone’ contains the phones of my customers
- Column ‘Purchases’ contains the number of times customers have bought in the last 12 months. Note that this value is provided for each date, so the 12 months time period is relative to the date we’re evaluating.
Finally, the column I am trying to produce is the ‘PREVIOUSPURCHASES’ which counts the number of times the figure provided in the column ‘Purchases’ has appeared in the previous 12 months (for each phone).
You can see on the SQL Fiddle example what I have achieved so far. The column ‘PREVIOUSPURCHASES’ is producing what I want, however, it is also producing lower values (e.g. only the maximum one is the one I need).
For instance, you can see that rows 4 and 5 are duplicated, one with a ‘PREVIOUSPURCHASES’ of 1 and the other with 2. I don’t want to have the 4th row, in this case.
I have though about replacing the row_number by something like max(row_number) but I haven’t been able to produce it (already looked at similar posts at stackoverflow…).
This should be implemented in SQL Server 2012.
Thanks in advance.
More Related Questions
- The equivalent of cross apply and select top I have table (script below):
if db_id ( 'CrossApplyDemo' ) is not null
drop database CrossApplyDemo
create database CrossApplyDemo
- Using Table valued Function in join There is a Table valued function which gives multiple rows as output and there are 2 columns as output.
Now I want use this function in the query for all the employees in the employees […]
- T-SQL Using Cross-Apply with Delete Statement I have the following tables:
and I need to delete the RecordsID rows, that are not included in […]
- SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry I have a strange scenario in which the auto identity int column in my SQL Server 2012 database is not incrementing properly.
Say I have a table which uses an int auto identity as a […]
- Does sequence contain 5 numbers that are each one apart solved recursively This is the data:
create table #t
insert into #t
- How to use CROSS APPLY XML PATH How can I use CROSS APPLY and FOR XML to normalise the table which contains a list for informartion?
It should be normalised […]
- When should I use Cross Apply over Inner Join? What is the main purpose of using CROSS APPLY?
I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are […]
- Unable to insert data into a certain table So I am literally a new DBA and my machine has not been properly setup yet so every time someone sends in a request for an unknown error I am in charge of searching google for answers […]
- SQL Server: split record I have a table like this:
account | check1 | check2
1 | 100]200]300 | 101]209]305
2 | 401]502 | 404]511
3 | 600 | […]
- SQL complex unions This is a little tricky to describe, but hopefully there is a solution.
I have a UDF which takes an ID and returns a table. Is there a way I can do a SELECT for these IDs and perform a […]