Questions tagged [top-n]
322 questions
1
vote
1 answer
TimescaleDB Top N function
I have been reading through the docs but cannot seem to find something similar to Prometheus' topk function.
Is there maybe a sort then limit?
As an example, lets say I wanted the top 10 hosts by cpu? Is that even possible?
Warren Paul
- 56
- 1
- 5
1
vote
1 answer
How to sort bars after grouped top_n in facet_wrap with ggplot2?
I'm facing an issue with sorting bars when using facet_wrap (which is commonly reported here, here and others) after group variables and get top values.
When I run the code without factor conversion, bars are ordered:
iris %>%
gather(key =…
patL
- 2,259
- 1
- 17
- 38
1
vote
2 answers
Hive joining columns with milliseconds
I have a table having columns id,create_time,code.
create_time column is of type string having timestamp value in the format yyyy-MM-dd HH:mm:ss.SSSSSS
Now my requirement is to find the latest code(recent create_time) for each id. If the…
Vanaja Jayaraman
- 753
- 3
- 18
1
vote
2 answers
Need to create procedure to find dept with most emps
I need to create a procedure to find the department name of the department with the most employees.
I am not allowed to use temp. tables as those were not seen in my course.
Code below
CREATE OR REPLACE PROCEDURE grootste_dept
IS v_department …
user11929681
1
vote
1 answer
Finding top n-th occurrences in group, Hive
I have a table where each record have columns: title and category.
I want to find 2 titles with most occurrences in their category. Some titles are listed in both categories. How can this be achieved in Hive?
Here is a table creation query:
create…
sergeda
- 2,061
- 3
- 20
- 43
1
vote
1 answer
Excluding tuples based on maximum condition
I have been trying to answer to solve this SQL Query problem, but got no success. The problem is the following:
PROBLEM:
Given 4 tables, PRODUCTS, REPAIRS, OWNERS and MALFUNCTION, for each product Brand and Model display the type of malfunction…
King Powa
- 441
- 3
- 9
1
vote
1 answer
Picking up latest 2 records from table in hive
Team, I have a scenario here.
I need to pick 2 latest record through Hql.
I have tried rownumber but does not seems to be getting expected out put
Select
A.emp_ref_i,
A.last_updt_d,
A.start_date,
case when A.Last_updt_d=max(A.Last_updt_d) over…
Ma28
- 111
- 7
1
vote
4 answers
Very slow query with TOP and ORDER BY
I have a query in SQL Server 2014 that takes a lot of time to get the results when I execute it.
When I remove the TOPor the ORDER BYintructions, it executes faster, but if I write both of them, it takes a lot of time.
SELECT TOP (10) A.ColumnValue…
Alberto Muñoz Sánchez
- 161
- 1
- 5
- 14
1
vote
0 answers
MySQL top-N ranking for each group, based on latest group's rows
For each group of TS, there's random number of rows that having different Name and it's Count. I would like to do the following:
Get latest group of TIMESTAMP with NAME order desc by Count. For more than 3 rows in the latest group, the rest sum by…
CheeHow
- 875
- 4
- 12
- 27
1
vote
1 answer
MySQL top-N ranking and sum the rest of same group
I've researched most of the time with this topic, however I couldn't get a efficient and perfect answer regarding ranking (top 3) a MySQL table with group and aggregate using sum() to the rest.
The data are as following:
TS | Name |…
CheeHow
- 875
- 4
- 12
- 27
1
vote
3 answers
I want to find the last transaction for each account prior to a certain date
I have a table which is defined (on Azure SQL Server) as follows:
CREATE TABLE dbo.[transaction]
(
id INT IDENTITY(1,1) NOT NULL,
[date] DATETIME NULL,
amount FLOAT NULL,
balance FLOAT NULL,
account_id INT NULL,
CONSTRAINT…
Dirk R
- 602
- 7
- 14
1
vote
3 answers
Oracle SQL join query to find highest salary
So I have two tables salary and emp whose definition is shown as below
[
I am tring to create a query that Find the employee who draws the maximum salary and Display the employee details along with the nationality.
I created this query
select…
MrLazyStudent
- 13
- 2
- 5
1
vote
2 answers
Get a entire row based on a particular condition on adjoining column in SQL Server
My table
integer | party | value | and many more columns
----------+---------+-------+----------------------
1 | abc | 40 | -----
1 | abc | 90 | -----
2 | pqr | 12 | -----
1 | abc | 120 …
user10338591
- 11
- 1
1
vote
2 answers
How to extract the first 5 rows for each id user in Oracle?
I have only one table where I want to extract the first 5 rows for each ID_USER.
I execute the following query:
SELECT
ID_USER as U_ID_USER,
COUNT(ID_USER) as NUM_TIC
FROM
TABLE_USERS
GROUP BY ID_USER
ORDER BY ID_USER
Which returns…
Y2KSAVE
- 55
- 5
1
vote
2 answers
How To Average Top Or Bottom 'n' Values In Python
How do you calculate an average top or bottom 'n' Values in Python? Example below, column = c2 calculates the average of the top 2 in the last 4 days.
c0 c1 c2
1 2 na
2 2 na
3 3 na
4 5 4
5 6 5.5
6…
mark
- 95
- 11