python (65.2k questions)

javascript (44.3k questions)

reactjs (22.7k questions)

java (20.8k questions)

c# (17.4k questions)

html (16.3k questions)

r (13.7k questions)

android (13k questions)

Questions - greatest-n-per-group

SQL Top N per group each year without using row_number / rank or variables

I have a limitation of not being able to use window functions or variables due to tool constraints. (Sorry couldn't elaborate much) but is there any scalable way to get Top N per group for each year? ...
test-img

unacorn

mysql

sql

greatest-n-per-group

Votes: 0

Answers: 1

Latest Answer

is there any scalable way to get Top N per group for each year? Use 2 table copies. Like this: SELECT group_id, t1.value FROM test t1 JOIN test t2 USING (group_id) -- group == rows with the...
test-img

Akina

Select Only the Most Recent Row

I'm using a query to pull the valuation cost of projects. It works well, except sometimes it returns multiple rows for a valuation. How can I modify my query to get only the most recent record to avoi...
test-img

GTS Joe

sql

oracle

greatest-n-per-group

oracle12c

Votes: 0

Answers: 2

Latest Answer

You can do a group by on the valuation and select the max date like below SELECT to_char( BVALUATN.G3_CALC_VALUE, '$999,999,999,999,999' ) AS "Valuation", MAX(TO_CHAR( G6ACTION.G6_AC...
test-img

Shanaya

How can I retrieve distinct data by using group by

I have following table and its sample data as follows, +------------------+-------+--------+---------------------+-------+ | PhoneCode | SeqID | Active | Token | CUSID | +--------...
test-img

NAKED_DEVELOPER

sql

oracle

greatest-n-per-group

Votes: 0

Answers: 5

Latest Answer

You can do it without GROUP BY and without self-joins: SELECT PHONECODE, SeqID, ACTIVE, Token FROM ( SELECT d.*, ROW_NUMBER() OVER (PARTITION BY phonecode ORDER BY se...
test-img

MT0

DISTINCT nor GROUP BY doesn't work to return unique values

SELECT DISTINCT order_reference as barcode, mth.location as location, mth.last_update_ts as updated FROM sales_order_header soh INNER JOIN manufacturing_tracking_history mth ON soh.id = mt...
test-img

Pat

sql

greatest-n-per-group

Votes: 0

Answers: 1

Latest Answer

I think you can do that by using Row_number() like this: select barcode, location, updated from ( SELECT order_reference as barcode, mth.location as location, mth.last_update_ts as updated, ro...
test-img

sa-es-ir

Posts

Questions

Blogs

Jobs