analytic-functions Questions
7
Solved
Let's assume I extract some set of data.
i.e.
SELECT A, date
FROM table
I want just the record with the max date (for each value of A). I could write
SELECT A, col_date
FROM TABLENAME t_ext
...
Immunology asked 17/1, 2012 at 16:19
2
Solved
My query returns many (thousands of) rows.
Column l has certain value for very small amount of rows (up to 10).
For each such row I want to output aggregated comma-separated values of very short (u...
Sennacherib asked 9/2, 2021 at 21:37
9
Solved
With the following MySQL table:
+-----------------------------+
+ id INT UNSIGNED +
+ name VARCHAR(100) +
+-----------------------------+
How can I select a single row AND its position amongst t...
Laborsaving asked 1/9, 2010 at 2:33
3
Solved
I have written a complex SQL query with Oracle in which I want to use two analytic function over the same partition.
Let's be quite simple but not too much:
SELECT col1,
MAX(col2) OVER(PARTITION...
Safekeeping asked 29/3, 2011 at 13:11
1
Solved
I have searched others' code and tried to customize in my case,
but it seemed when the question was the "max", it worked.
When it comes to find the top 100, it was not working.
I am trying to get t...
Hepzi asked 13/7, 2019 at 17:13
4
Solved
I have the following data, composed of the A value, ordered by MM (month).
The B column is computed as GREATEST(current value of A + previous value of B, 0) in a spreadsheet-like fashion.
How can...
Warford asked 3/9, 2012 at 7:17
3
Solved
for SQL Server 2008 R2
I have a resultset that looks like this (note [price] is numeric, NULL below represents a
NULL value, the result set is ordered by product_id and timestamp)
product times...
Viewy asked 12/8, 2011 at 18:59
2
I have data like this
id cp_id amount_a amount_b
CCP1 TTP01 10.000.000 2.000.000
CCP1 TTP02 10.000.000 3.000.000
CCP1 TTP03 10.000.000 1.000.000
CCP1 TTP04 10.000.000 500.000
CCP2 TTP05 5.000.000 ...
Weissman asked 26/1, 2017 at 7:51
3
Solved
I'm working with some data that is currently stored in 1 minute intervals that looks like this:
CREATE TABLE #MinuteData
(
[Id] INT ,
[MinuteBar] DATETIME ,
[Open] NUMERIC(12, 6) ,
[High] NUM...
Freitas asked 24/1, 2017 at 23:28
3
Solved
I am writing a query to fetch records from a Oracle warehouse. Its a simple Select Query with joins on few tables and i have few columns to be aggregated. Hence i end up using Groupby on rest of th...
Numbersnumbfish asked 27/7, 2011 at 16:21
4
Solved
I have a SQL query written by someone else and I'm trying to figure out what it does. Can someone please explain what the Partition By and Row_Number keywords does here and give a simple example of...
Uterus asked 7/5, 2012 at 5:28
4
Background
I know that in Oracle it's possible to create custom aggregate functions that process a collection of values and return a single result. Edit: I've even read the friendly manual at docs...
Joshi asked 25/8, 2015 at 18:11
2
Solved
I'm fairly experienced with Oracle analytic functions but this one has stumped me. I'll kick myself if there's an obvious solution :)
I have a table, JOURNAL, which records Inserts, Updates and De...
Aviate asked 2/12, 2011 at 2:2
2
Solved
I am just starting on Teradata and I have come across an Ordered Analytical Function called "Rows unbounded preceding" in Teradata. I tried several sites to learn about the function but all of them...
Rickie asked 16/6, 2015 at 8:1
1
Solved
I want to find the cumulative or running amount of field and insert it from staging to table. My staging structure is something like this:
ea_month id amount ea_year circle_id
April 92570 1000 201...
Spectacle asked 3/4, 2014 at 14:47
1
Solved
In this answer (with all executable simplified and documented sample code + helpful comments)
I did a kind of a trick there to calculate the last two rows of the following table:
DESCR SUM
-----...
Harbard asked 21/2, 2014 at 16:54
2
Solved
I'm searching for any NoSQL system (preferably open source) that supports analytic functions (AF for short) like Oracle/SQL Server/Postgres does. I didn't find any with build-in functions. I've rea...
Dakota asked 31/10, 2012 at 11:3
1
Solved
Is it possible to use windowing with any of the percentile functions? Or do you know a work around to get a rolling percentile value?
It is easy with a moving average:
select avg(foo) over (order...
Inviolable asked 20/7, 2012 at 15:54
3
Solved
Is there a way I can reduce the impact of the 'ORDER BY lro_pid' clause in the OVER portion of the inner query below?
SELECT *
FROM (SELECT a.*,
Row_Number() over (PARTITION BY search_point_typ...
Footton asked 2/6, 2011 at 22:18
1
We are using ROW_NUMBER() in our query. It is returning correct results in almost all scenarios. But for 1 user, it is behaving very differently.
With #TEST as (
select top 50
ROW_NUMBER() over...
Reckford asked 16/11, 2011 at 6:0
1
Solved
This particular case was distilled from an example where the programmer assumed that for two shipments into a tank car, line #1 would be loaded first. I corrected this to allow for the loading to b...
Ventilator asked 12/9, 2011 at 20:59
2
Solved
In Oracle 11g we need to be able to query a table to pull out information from rows with the highest and lowest values in a certain group. For example using the EMP table we'd like to find the name...
Vacuity asked 4/5, 2011 at 8:35
3
Solved
Here is some example data in a mysql table
a b distance
15 44 250
94 31 250
30 41 250
6 1 250
95 18 250
72 84 500
14 23 500
55 24 500
95 8 500
59 25 500
40 73 500
65 85 500
32 50 500
31 39 500
22 ...
Verenaverene asked 21/5, 2010 at 22:40
1
Solved
In which SQL standard was RANK() first introduced?
List of SQL standards:
SQL-86
SQL-89
SQL-92
SQL:1999
SQL:2003
SQL:2008
SQL Rank function: http://en.wikipedia.org/wiki/Select_(SQL)#RANK.28.2...
Duck asked 4/3, 2010 at 1:25
2
I have a table called 'highscores' that looks like this.
id udid name score
1 1111 Mike 200
2 3333 Joe 300
3 4444 Billy 50
4 0000 Loser 10
5 DDDD Face 400
Given a specific udid, I want to return...
Conservatory asked 28/1, 2010 at 21:6
1 Next >
© 2022 - 2024 — McMap. All rights reserved.