logo
down
shadow

sql oracle select group by


sql oracle select group by

By : Gung Na
Date : November 14 2020, 04:48 PM
With these it helps I have two tables by names CASTR and CABL. My query is , I would suggest using analytic functions, something like this:
code :
SELECT to_number(substr(CASTR_OPER_DATE, 1, 5)), CABL_REL_DAY, CASTR_KW_CAAC, cabl_avail_bal
FROM (SELECT CASTR_OPER_DATE, CABL_REL_DAY, CASTR_KW_CAAC, cabl_avail_bal,
             row_number() over (partition by CASTR_KW_CAAC order by cable_rel_day desc) as seqnum
      FROM CASTR INNER JOIN
           CABL 
           ON CASTR_KW_CAAC = CABL_KW_CAAC
      WHERE CABL_REL_DAY <= to_number(substr(CASTR_OPER_DATE, 1, 5))   
     ) c
WHERE seqnum = 1;


Share : facebook icon twitter icon
ORACLE SELECT GROUP BY + something that I don't know

ORACLE SELECT GROUP BY + something that I don't know


By : Tran Binh Duong
Date : March 29 2020, 07:55 AM
With these it helps I have a table in the ORACLE database, details below: , you can solve this by analytics:
code :
SQL> select fruit_name, min(growth_time) || '-' || max(growth_time) growth_time, growth_place
  2    from (select fruit_name, growth_place, growth_time,
  3                 max(grp) over(partition by fruit_name, growth_place order by growth_time) grp
  4             from (select fruit_name, growth_time, growth_place,
  5                          case
  6                            when to_date(lag(growth_time, 1)
  7                                   over(partition by fruit_name, growth_place order by growth_time), 'hh24mi')
  8                                 < to_date(growth_time, 'hh24mi') - (30/1440)
  9                            then
 10                              row_number() over(partition by fruit_name, growth_place order by growth_time)
 11                            when row_number() over(partition by fruit_name, growth_place order by growth_time) = 1
 12                            then
 13                              1
 14                          end grp
 15                      from fruits))
 16   group by fruit_name, growth_place, grp
 17   order by fruit_name, growth_time
 18  /

FRUIT_ GROWTH_TIME                              GROWTH
------ ---------------------------------------- ------
apple  0600-0800                                shelf3
melon  0600-0800                                shelf1
orange 0600-1000                                shelf5
orange 1200-1400                                shelf5
lag(growth_time, 1) over (partition by fruit_name, growth_place order by growth_time)
SQL> select fruit_name, growth_time, growth_place,
  2         case
  3           when to_date(lag(growth_time, 1)
  4                over(partition by fruit_name, growth_place order by growth_time), 'hh24mi')
  5                < to_date(growth_time, 'hh24mi') - (30/1440)
  6           then
  7             row_number() over(partition by fruit_name, growth_place order by growth_time)
  8           when row_number() over(partition by fruit_name, growth_place order by growth_time) = 1
  9           then
 10             1
 11         end grp
 12    from fruits;

FRUIT_ GROW GROWTH        GRP
------ ---- ------ ----------
apple  0600 shelf3          1
apple  0630 shelf3
apple  0700 shelf3
apple  0730 shelf3
apple  0800 shelf3
melon  0600 shelf1          1
melon  0630 shelf1
melon  0700 shelf1
melon  0730 shelf1
melon  0800 shelf1
orange 0600 shelf5          1
orange 0630 shelf5
orange 0700 shelf5
orange 0730 shelf5
orange 0800 shelf5
orange 0830 shelf5
orange 0900 shelf5
orange 0930 shelf5
orange 1000 shelf5
orange 1200 shelf5         10
orange 1230 shelf5
orange 1300 shelf5
orange 1330 shelf5
orange 1400 shelf5
SQL> select fruit_name, growth_place, growth_time,
  2         max(grp) over(partition by fruit_name, growth_place order by growth_time) grp
  3    from (select fruit_name, growth_time, growth_place,
  4                 case
  5                 when to_date(lag(growth_time, 1)
  6                        over(partition by fruit_name, growth_place order by growth_time), 'hh24mi')
  7                      < to_date(growth_time, 'hh24mi') - (30/1440)
  8                 then
  9                   row_number() over(partition by fruit_name, growth_place order by growth_time)
 10                 when row_number() over(partition by fruit_name, growth_place order by growth_time) = 1
 11                 then
 12                   1
 13               end grp
 14          from fruits);

FRUIT_ GROWTH GROW        GRP
------ ------ ---- ----------
apple  shelf3 0600          1
apple  shelf3 0630          1
apple  shelf3 0700          1
apple  shelf3 0730          1
apple  shelf3 0800          1
melon  shelf1 0600          1
melon  shelf1 0630          1
melon  shelf1 0700          1
melon  shelf1 0730          1
melon  shelf1 0800          1
orange shelf5 0600          1
orange shelf5 0630          1
orange shelf5 0700          1
orange shelf5 0730          1
orange shelf5 0800          1
orange shelf5 0830          1
orange shelf5 0900          1
orange shelf5 0930          1
orange shelf5 1000          1
orange shelf5 1200         10
orange shelf5 1230         10
orange shelf5 1300         10
orange shelf5 1330         10
orange shelf5 1400         10
How to select (i.e. second) a group in SQL (Oracle)

How to select (i.e. second) a group in SQL (Oracle)


By : user4282891
Date : March 29 2020, 07:55 AM
this will help my column look like this: , You can use the row_number() analytic function for that:
code :
select your_col
  from (select your_col,
               row_number() over (order by your_col) as rn
          from your_table
         group by your_col)
 where rn = 2
Select - by ID of a group - in SQL Oracle

Select - by ID of a group - in SQL Oracle


By : Robin Rumeau
Date : March 29 2020, 07:55 AM
hop of those help? I have this query that selects several columns, groups and assigns IDs to those groups. Now, my final step would be to select a group by ID. But for some reason it doesn't work. , You need to use a subquery or CTE:
code :
select t.*
from (select MYCOLUMN, col2, sum(cal_a+col_b), col3,
             count(col4) as col4sum,
             DENSE_RANK() OVER (ORDER BY MYCOLUMN) as GROUPID
      from my_tab
      group by MYCOLUMN, col2, col3
     ) t
where GROUPID = 2;
Oracle SQL: Select a Max and Min Value from a Group

Oracle SQL: Select a Max and Min Value from a Group


By : Behnam karimi
Date : March 29 2020, 07:55 AM
I wish this helpful for you From multiple tables I'm currently selecting a product, value, and contract period. I want to group the results by product and shipment period while summing the value. , Oracle Setup:
code :
CREATE TABLE table_name ( product, value, ship_period_descr, arrival_period_begin, arrival_period_end, ship_period_begin, ship_period_end ) AS
SELECT 'Apples', 600, 'X', NULL, NULL, DATE '2016-02-01', DATE '2016-03-15' FROM DUAL UNION ALL
SELECT 'Apples', 700, 'X', NULL, NULL, DATE '2016-03-01', DATE '2016-04-16' FROM DUAL UNION ALL
SELECT 'Lemons', 200, 'X', NULL, NULL, DATE '2016-01-15', DATE '2016-01-31' FROM DUAL UNION ALL
SELECT 'Lemons', 150, 'X', NULL, NULL, DATE '2016-02-01', DATE '2016-02-15' FROM DUAL UNION ALL
SELECT 'Lemons', 200, NULL, DATE '2016-02-15', DATE '2016-02-28', NULL, NULL FROM DUAL UNION ALL
SELECT 'Lemons', 250, NULL, DATE '2016-03-01', DATE '2016-03-15', NULL, NULL FROM DUAL;
SELECT   Product,
         SUM( Value ) AS Value,
         DECODE(
           DECODE( P.SHIP_PERIOD_DESCR, NULL, 1, 0 ),
           1, 'ARRIVE ' || MIN( P.ARRIVAL_PERIOD_BEGIN ) || ' - ' || MAX( P.ARRIVAL_PERIOD_END ),
              'SHIP ' || MIN( P.SHIP_PERIOD_BEGIN ) || ' - ' || MAX( P.SHIP_PERIOD_END )
         ) AS Contract_Period
FROM     table_name p
GROUP BY Product,
         DECODE( P.SHIP_PERIOD_DESCR, NULL, 1, 0 );
PRODUCT      VALUE CONTRACT_PERIOD                              
------- ---------- ----------------------------------------------
Apples        1300 SHIP 01-FEB-16 - 16-APR-16                     
Lemons         350 SHIP 15-JAN-16 - 15-FEB-16                     
Lemons         450 ARRIVE 15-FEB-16 - 15-MAR-16                   
Oracle SQL, how to select first row in a group?

Oracle SQL, how to select first row in a group?


By : abedi
Date : March 29 2020, 07:55 AM
I wish this helpful for you Here is the my SQL fiddle: http://sqlfiddle.com/#!4/75ab7/2 , You can use keep and first in oracle:
code :
select seq_num,
       max(trim(id)) keep (dense_rank first order by trim(id)) as first_id,
       max(trim(name)) keep (dense_rank first order by trim(id)) as first_name
from subject
group by seq_num
order by seq_num asc;
Related Posts Related Posts :
  • How to create a database snapshot in SQL Server 2008 R2
  • Query speed and expressions with constant value
  • How to do a complex calculation as this sample
  • Passing a ResultSet into a Postgresql Function
  • Netsuite custom formula field using a CASE statement with multiple WHEN conditions returning "invalid expression&qu
  • Execute query command (dynamic sql) runs faster than ordinary query
  • How to extract only numbered rows from a column
  • INSTEAD OF DELETE trigger (Postgresql)
  • Oracle Apex, when a report with sql is created, how do i get it return after entering in to a text box?
  • Select records based on two tables where one column in one table starts with the records from another tables column usin
  • How to write select using two ids combination in Oracle 11g
  • Frequently use of 'UPDATE STATISTICS WITH FULLSCAN' is normal?
  • need query for this simple pulling data sql server
  • Sql query by using decode to show grades of employee
  • Not a single function error in Oracle
  • Oracle SQL optimizer's behavior when dealing with ORs and row-independent predicates (such as functions returning same v
  • Combining output of two or more select statement
  • Must Declare Scalar Variable Exception
  • Postgres Calculate Difference Using Window Functions
  • SQL Server : unsure how to retrieve selected records
  • SQL unique field: concurrency bugs?
  • Query to get the next identity?
  • SQL Server Adding summing values based on the month
  • Join on month if available else join on latest month
  • How to solve error: ORA-01406 fetched column value was truncated?
  • Selecting Field Multiple Times using OR
  • Which SQL datatype can be used to store mobile numbers in numeric forms, without including characters like brackets and
  • Oracle SQL - "Not a group by expression" after adding CASE statements
  • Runnning total of unique string values in column over days
  • VB.Net to AccessDB by SQL - INSERT INTO with parameters error
  • SQL/Excel/VBA - UPDATE query: 'Syntax Error (missing operator) in query expression'
  • Code to combine two sql queries
  • Setting SQL Date field value based off Week Number and Day Number Field
  • SQL query to find matching records but with differences in case sensitivity?
  • How to sort record inside views in SQL Server 2008
  • How to link the output of teradata sql query to an excel sheet?
  • Matching sets in SQL when cross-referencing
  • error even after following Microsoft format in SQL
  • Increase price by percentage, results rounded
  • Can we Use "Case" in a ColdFusion Query-of-Query
  • displaying 'Active' for a row which has 'A' as value
  • Using pattern search in SQL Server Query
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org