1,250 questions
4
votes
8
answers
292
views
How to extract a given number of ordered rows from a given number of randomly selected samples?
Starting with the example dat0 below, let's say I want to randomly extract 3 pairs of ìd`s.
Initial data (5 id pairs)
dat0 <-
structure(list(id = c("A", "A", "B", &...
- 972
-1
votes
1
answer
68
views
mySQL how to increment by a group ---why doesn't this work?
I have a table in mySQL like the below, and I'm trying to assign an incremented number to the group based upon ID's, but it's not working.
My query:
select row_number() over (partition by IDs)
from ...
-1
votes
1
answer
87
views
row_number giving me trouble when called from a join [closed]
When I run the code below I get no results, but if I remove the b.row field from the first select statement, I do get the expected result set. What I want ultimately, is to have a column in the result ...
- 101
0
votes
1
answer
83
views
Setting a row number for each row in PySpark Dataframe
Currently I'm working with a large database using PySpark and stuck with a problem oh how to correctly set row numbers depending on condition
My dataframe is:
id_company id_client id_loan date
c1 ...
- 333
0
votes
0
answers
152
views
ROW_NUMBER() OVER (PARTITION BY [variable] ORDER BY NEWID() - seems to select the same output every time and not random
I have a table with over 1.8 million rows and around 200 columns, which I will call [DBO].[DATA]. The only columns in this table that are really relevant to this query are [TEMP_PERSON_ID], [AGE] and [...
- 25
1
vote
1
answer
97
views
SQLite query to compute row number without row_number etc
I have a table of string values where the natural sort order is first by length and then by value.
i.e.,
CREATE TABLE t(v VARCHAR(255));
INSERT INTO t(v) VALUES ('100'), ('10'), ('8'), ('5'), ('3'), (...
- 2,408
1
vote
0
answers
53
views
MariaDb, calculating median value for limited number of values from linked table
Having two tables, representing phones and their out-coming calls, I'd like to get list of phone numbers along with median value of last 5 call duration, in case there is at least 5 calls for number ...
- 11
1
vote
1
answer
66
views
Rank rows by specified partition
This is my fiddle
I have a table like this
create table CTE1 (
[CC Receiver] VARCHAR(10),
Name VARCHAR(255),
[Division] varchar(10),
[Old Block] varchar(10),
[Date] Date,
Round ...
- 61
0
votes
1
answer
80
views
SQL Query to Rank Customers Based on Diversity of Cuisines Tried
I need help with a SQL query that ranks each customer based on the diversity of cuisines they have tried. The rank should be unique (not repeated) and should not skip any values. The goal is to ...
- 15
-4
votes
1
answer
33
views
row_number could not be used in array_to_string(array_to_agg function
I've emp table which contains name & name2 field.
I need the output in a single line.
As i dont have unique id(emp_id) in this table, i am getting error as below :-
ERROR: aggregate function ...
0
votes
1
answer
59
views
Numbering rows in Pandas without order [duplicate]
I have a table Mapping that looks like this
Mapping
+---------+---------+
| user_id | prod_id |
+---------+---------+
| 41 | N12ad |
| 2 | nd5Ed |
| 73 | bDe4d |
| 25 | ...
- 910
4
votes
2
answers
386
views
In Polars: what is the correct equivalent code for row_number() over(partition by) in BigQuery SQL?
I am trying to refactor (translate) a given SQL query to python script using polars library.
I am stuck in one line of query where ROW_NUMBER() function is used followed by OVER(PARTITION BY) ...
0
votes
3
answers
102
views
How do you get ROW_NUMBER to start over at different value between equal values in the partition?
Please see below for what I have and what I would like to have. I have tried a subquery using LEAD and LAG to create a flag for partitioning but couldn't get that to work.
Thanks.
CREATE TABLE #t (...
- 29
2
votes
1
answer
122
views
How can I improve performance of this row_number() ranking window function query?
Table user_power_rank:
name
type
pk
id
string
1
userid
integer
0
power
integer
0
atime
integer
0
Indexes of this table:
index name
columns
i_power_desc_atime_asc
power desc, atime asc
i_id_power
id, ...
- 917
0
votes
1
answer
70
views
How can I separate RowNumber counting?
I'm trying to create a point in time hierarchy in my organization. The problem I'm running into are some folks have reported to the same manager more than once in their career. I tried doing a ...
- 247
1
vote
2
answers
93
views
Check value between multiple rows in SQL
I need to write a query to output a table with 2 values - number & description.
Table 1 has 2 columns - id and date
id date
101 04-07-2018
102 15-11-...
- 4,228
0
votes
0
answers
51
views
R: row_number not working correctly with pivot_wider? [duplicate]
I’m getting stumped on this one. I have a data frame that I’m trying to reshape into wide form using pivot_wider, but the results aren’t as expected.
My data is currently structured like this:
One ...
- 1
0
votes
1
answer
127
views
Convert row_number to SQL join and top1 functions
I am looking for this query to be re-written using sub-queries, top 1 or max function, however due to the nature of data in underlying tables, not able to achieve it. Appreciate if you can help on ...
1
vote
1
answer
87
views
SQL Server Row_Number() sequence not skipping NULL or 0 values to calculate working days
I am attempting to create a Working Days field in an existing date dimension in SQL Server to extract the nth working day of a month. I have researched solutions so far and have created a field for ...
- 65
1
vote
1
answer
111
views
Pyspark Skewed Data Deduplication Approach
I'm trying to dedupe some large skewed data, of uids, timestamps, and another set of session_ids. The second set of IDs somewhat low cardinality. I'm trying to dedupe the Spark DF across these 3 ...
- 115
0
votes
1
answer
42
views
LEFT JOIN on ROW_NUMBER OVER PARTITION
I have two tables :
1.Actions : Contains multiple actions performed on orderIDs on specific dates. For each orderID, there are multiple rows, each representing a different action performed on a ...
-5
votes
1
answer
145
views
how to display serial number in sql
How to display the serial number for the below
select count(*) toatl_count, "employee is Invalid" error from emp
union
select count(*) total_count, "comapny name is incorrect" ...
- 27
4
votes
4
answers
134
views
Is it possible to create ROW_NUMBER-column with repeating values?
I need to create an id-column: on every TRUE statement it should be a normal serial number, but on every FALSE statement the field value should contain the previous number in this column. In other ...
- 49
1
vote
3
answers
103
views
Unique number to every section in "partition by"
My table:
"letter" "time"
"a" "2024-08-14 16:57:56.563112"
"b" "2024-08-14 17:57:56.563112"
"b" "2024-08-14 18:57:56....
0
votes
2
answers
55
views
How can I get more than records in Jupyter?
My query records in Clickhouse has about 60m records but when I run in Jupyter it loads only 5000 records. How can I have all records in Jupyter?
I need all data of the query result and then make a ...
3
votes
2
answers
601
views
How to filter a query based on row number using LINQ with Entity Framework
I have the following query in MySQL (version 8+)
SELECT MachineId, TotalProduction
FROM (
SELECT MachineId,
TotalProduction,
ROW_NUMBER() OVER (PARTITION BY DATE(InsertedAt) ...
0
votes
0
answers
94
views
DataTables: get the row number of a paginated result from a php script
I have an index.html page showing a table where the results are obtained by DataTables with server-side processing.
The results are paginated, and each row has 8 columns: the first column shows an ID ...
- 61
0
votes
1
answer
255
views
row_number() with alias doesn't work in H2
I'm writing a query using Querydsl which involves the row_number() window function. The following query works fine:
select row_number() over (partition by id)
from example_table ExampleTable
but as ...
- 1,932
0
votes
1
answer
32
views
I would like to add a column rownumber counter based on the InvoiceId only in SQL
Based on the InvoiceId, I would like to count 1 to X and add those line item values next to each order line item.
Desired result wanted. Adding LineItem column, then counting 1 to X until next ...
- 19
-1
votes
1
answer
76
views
Why can't I use the row number on my where statement? [duplicate]
In the following CTE, outside it, I try to use the RowNum, but it is not recognized.
;with cte as(
select *
,min([Close]) OVER(PARTITION BY YEAR(Date),Crypto) AS 'Min'
,max([Close]) OVER(PARTITION BY ...
- 3
1
vote
1
answer
181
views
Row_Number(), Offset, Fetch_Next and Restarting Numbering
I have the following
A pageheader table
A pagecontent table
An items table
I have a loop that creates a header record in the pageheader, and then a separate insert takes the next 25 records from the ...
- 33
0
votes
1
answer
1k
views
Finding Duplicate Records Only Using ROW_NUMBER() in Google Bigquery
I want to find "name_org1" records that are duplicated - no unique records. I am operating in Google Bigquery.
To do this I decided to use the ROW_NUMBER() windows function to rank all ...
1
vote
1
answer
69
views
How to get table with only rows of maximum value in a column partitioned by another column?
I'm trying to get the maximum value of calories consumed per day. I can't figure out how to get a query result which I imagine to just be 1 row for each date, with a column containing the value for ...
- 21
0
votes
1
answer
847
views
How to write row_number with partition by in qlik table?
I want to write below row_number logic in qlik table. How can it be written?
row_number() over (partition by job.jobnumber order by cast(hist.EventTimeStamp as Date) asc) as row_id
How to write ...
- 11
1
vote
4
answers
330
views
How can i obtain the row number of the first item in a filtered table with VBA Excel
I have the following in the attachment
i tried to use the following macro to obtain the number of row of the first item when i filter this list to "Retail" only. the macro should show a ...
- 11
1
vote
1
answer
65
views
How can I use ROW_NUM() to pick one instance where it doesn't have a second record?
I am trying to use ROW_NUM() to get me instances of ID's that have only 1 associated comment value which is like 'SEND Final' and that's it.
This is an example of the records in a table:
Comment
ID
...
- 91
0
votes
1
answer
144
views
JOIN for specific row number
I am using Postgres and would like to do a table join on a specific row number.
I have the following:
LEFT JOIN trip_approver_snapshot tas ON trip.trip_id=tas.trip_id
and select * from (select ...
- 9,005
0
votes
2
answers
1k
views
Snowflake row_number function is returning inconsistent results between CTE and view
I currently have a dataset, where it is possible for an person to have multiple records in one month, and I only want to take the first. To do this, I am using the row_number function, and my code is ...
- 19
0
votes
1
answer
87
views
Extract the status start date and change date for a given status code Teradata
I have the data in the following format with several Status codes with their dates. The codes are in question are 3 and 8. I need to find the start date when STcode=3/8 and use it as STDATE and if ...
- 593
0
votes
1
answer
344
views
Properly implement DENSE_RANK() or similar function
I am trying to get an output using SQL Server 2016 that will return StudyID's that only have an AssignmentType of "Final" i.e. no AssignmentType of "Draft".
Some studies may have a ...
- 91
-1
votes
1
answer
122
views
Row number that resets numbers based on a condition
Hi a have a dataframe with some user_id, the months that they are active, and the lead_month which they're active.I need to perform the calculation of column "active_months" shown at the ...
-3
votes
1
answer
79
views
Merge two tables in horizontal without common column in Mysql
I am practising with sakila database in MySQL. There is a table name actor with a column last_name. I want to list all last_name which is not repeated in first column, and repeated in second column.
...
- 67
0
votes
1
answer
88
views
DB2 Distinct Count
How to Count(Distinct ID) where the Value is equal 1; possibly using
ROW_NUMBER() OVER(PARTITION BY ... ) ?
- 543
1
vote
1
answer
68
views
Display the row index number given conditions
I am currently trying to practice with some data manipulation procedures and have faced with the problem of how to make subset based on special condition.
Let's assume that the dataframe looks like ...
- 333
0
votes
1
answer
236
views
How to generate row numbers for millions of rows returned from a view?
I have requirement of selecting data from view with definition of union all of multiple select statements (each table having 500K records) but want to generate row numbers for each of the records.
...
- 161
0
votes
1
answer
93
views
Fetch top 3 accounts by sales rep by region in Kusto
I'm new to Kusto and I'm trying to fetch top 3 accounts with largest sales amount by sales representative by sales region and I'm struggling to get this right. Imagine a table like this:
Sales_rep
...
- 143
0
votes
1
answer
35
views
when the window function row_number in CTE generate the result in hive
I'm using row_number window function in hive CTE(with clause)
with data(
select 1 a,1 b
union select 1,2
union select 1,3
union select 1,4
...
union select 1,26
),
data_with_row_num (
select a,b,...
- 118
0
votes
3
answers
165
views
SQL rownumber until column value changes back
I am trying to create a column that represents how many days ago the most recent promotion started for each product. It should continue counting on when the promotion ended, until the next promotion ...
- 11
0
votes
1
answer
93
views
"REAL WORLD" SQL CTE query capable of: getting a tree from a parent child table with depth-first & alphabetical ordering & filter/sum nodes subtrees
After searching hours over the internet, still missing a good example of a SQL CTE Query able to be used in real world scenarios, wondering even if CTE and MODERN SQL in general are currently capable ...
- 1
0
votes
1
answer
451
views
Returning the row number for a query to a different Google sheet
I am trying to retrieve the row number along with multiple cell data from a different sheet than the one I am in. I have the query that returns all of the column values I need but I can't seem to ...
- 11