FAQ Database Discussion Community


Row count on a SQL Server 2008 table to identify duplicates

sql-server-2008,common-table-expression,row-number
I have records which can have multiple addresses associated with them, I normalized all of these into a table so I can see every address belonging to a record and identify duplicates (based on address line 1). What I am trying to identify is a record that has the same...

How do you create a boarder in an SSRS Report?

reporting-services,group-by,border,hidden,row-number
I've gotten the hang of creating charts/colors and grouping data but I can't seem to figure out how to outline each grouping in my report. I've got data built for my chart - what I would like to have it appear as -- is like this: I'm not picky on...

SQL server ROW_NUMBER() OVER(PARTITION issue

sql-server,row-number
I have a table named SavingsMaster. Below is a sample output. +--------+------------------+-------+ | CustNo | AccountNo | Cycle | +--------+------------------+-------+ | 1 | 48SVAS1521144100 | | | 2 | 21SVSV1300854500 | | | 2 | 21SVAS1308476900 | | | 1 | 48SVAS1411737700 | | +--------+------------------+-------+ As you can see, both...

How do I use ROW_NUMBER in DB2 10 on z/OS?

db2,row-number,zos
I am running a SQL query and trying to break the results down into chunks. select task_id, owner_cnum from (select row_number() over(order by owner_cnum, task_id) as this_row, wdpt.vtasks.* from wdpt.vtasks) where this_row between 1 and 5; That SQL works with DB2 10.5 on Windows and Linux, but fails on DB2...

Add ROW_NUMBER to a LINQ query for specific Entity?

c#,.net,linq,entity-framework,row-number
I need an EF query to get the row number of a specific entity. I've looked at this, and this. Currently I have it working in this way: private DbContext Context; public int GetRowNumberQuery<TEntity>(int entityId) { var allEntities = this.Context.Set<TEntity>().ToList(); return allEntities .Select((entity, index) => new { Index = index,...

Giving a row number to mysql data based on date

c#,mysql,winforms,order,row-number
I have been trying to add a row number for my data i.e. each row would have a number based on my ordering, if I list them based on date, the one which is the oldest date would get row number 1 and so on regardless of their actual id...

I want to get the last record from oracle using rownumber()

sql,oracle,row-number
here am using oracle query for below record, i want to last rownumber 11 record. select max(t.atdatetime), t.lead, t.sysid, row_number() over(partition by t.sysid order by t.lead desc) as "number" from psd.psd_empreport t where t.sysid in(5350) group by t.lead, t.sysid order by 2 desc SNO datetime tlcode ecode rownumb 1 7/2/2013...

SQL Server : autoincrement for column, partitioned by values in another column

sql-server,row-number
Anyone have a solution for the following please? (the code snippets in both cases should be run as full blocks) The following code simulates the situation for a daily processing job. Here's day 1 Declare @poitem_source table (PONum int, txt varchar(50), status varchar(25)) Declare @poitem_destination table (PONum int, LineNum int,...

Trying to capture all of the Row_Numbers with Rows > 1

sql,sql-server,ssms,row-number
I'm sure this is actually a simple fix (it just sounds like it would be) but my brain cannot wrap around on how to get this to work. I'm currently using this query Select m.memberfirstname+' '+m.memberlastname [Name], m.MemberDOB, s.GroupID, c.PaperID, cr.PaperScanningStatusID, ROW_NUMBER() OVER(PARTITION BY m.memberfirstname+' '+m.memberlastname,m.MemberDOB,s.GroupID ORDER BY m.MemberDOB) rn...

SQL - Apply same ROW_NUMBER to non-matching rows

sql,row-number
I am using this query to calculate number of business days for certain date range: WITH cte AS ( SELECT [Date] AS WorkingDay, ROW_NUMBER() OVER (ORDER BY [Date] ASC) AS RN FROM DimDate WHERE IsHolidayUSA = 0 AND IsWeekday = 1 ) SELECT DateStarted, DateCompleted, c2.RN - c1.RN AS CycleTime...

case when based on group dense_rank() value and row_number() value

sql,sql-server-2008,row-number,case-when,dense-rank
here is a task i've been given to get the qty(quantity) based on the outer_index_group which i get from dense_rank col1,col2,col3 and inner_index_group from row_number of col1,col2,col3. Here rules: 1) inside each outer_index_group, if col4 has 4055 and 4086, qty i should get is in row inner_index_group 4086 which commonly...

Selecting last 2 rows of each dense_rank

sql-server,row-number
Using MSSQL, I am trying to get some information from a journal where one event happens directly after another event. So what I am effectively aiming for, is to get a row number partitioned by a TransactionID, and then I need the last 2 rows (last 2 row number) for...

Selecting SQL TOP N rows from group, row_number, rank not working

sql,sql-server,group-by,rank,row-number
In SQL Server, I have one table with following fields. I would like to get Top N rows from each group. I have tried following sql query with Rank() and Row_number() but rank and row numbers are not in sequence so not able to limit the rows. Here is SQL...

SQL QUALIFY equivalent HIVE query

sql,hive,oracle-sqldeveloper,hiveql,row-number
I'm trying to create a HIVE query from an Oracle SQL query. Essentially I want to select the first record, sorted descending by UPDATED_TM, DATETIME, ID_NUM. SELECT tbl1.NUM AS ID, tbl1.UNIT AS UNIT, tbl2.VALUE AS VALUE, tbl1.CONTACT AS CONTACT_NAME, 'FILE' AS SOURCE, CURDATE() AS DATE FROM DB1.TBL1 tbl1 LEFT JOIN...

Use Row_Number To Number Records in DB2 (And Reset Count on Change of ID)

sql,db2,row-number
What I'd like to do is number the records the query returns, but reset the number to 1 on the change of a particular field. For instance, I would like the Row_Number count to reset back to 1 each time DCACCT changes but increment by 1 when there are multiple...

How to get change points in oracle select query?

sql,oracle,max,min,row-number
How can I select change points from this data set 1 0 2 0 3 0 4 100 5 100 6 100 7 100 8 0 9 0 10 0 11 100 12 100 13 0 14 0 15 0 I want this result 4 7 100 11 12 100...

SQL server what is this over clause doing

sql,sql-server,row-number
I've inherited the following query and I'm not sure what the second value being selected is. Can anyone please shed some light? Sorry if the question is vague but I've given as many details as I understand. SELECT dateId, row_number() OVER (order by DATEPART(ISO_WEEK, min(myDate)) % 2, myDateId-1) AS DayIdx...

Writing an analytic function to mark last date of some column

sql,oracle,oracle11g,row-number
Can you help me to write an analytic function that marks the last date a client's service was stopped. For example one client has 2-3 stops of his service, and I would like to count how many stops there are, and to mark last date of stopping. I'm using SELECT...