Sitemap

Amicuk Programming Answers

All selected 'n' consecutive rows in retrieved in a single query

2015-10-11   Views:0

Advertisement

hello, I have table T with 50,000 rows create table T (student_id number, class_id number, quiz_id number, marks number) some sample rows like INSERT INTO T VALUES (1,1, 1, 50); INSERT INTO T VALUES (2,2, 2, 40); INSERT INTO T VALUES (3,1, 3, 34); IN

hello,
I have table T with 50,000 rows
create table T
(student_id number,
class_id number,
quiz_id number,
marks number)
some sample rows like
INSERT INTO T VALUES (1,1, 1, 50);
INSERT INTO T VALUES (2,2, 2, 40);
INSERT INTO T VALUES (3,1, 3, 34);
INSERT INTO T VALUES (1,1, 4, 10);
INSERT INTO T VALUES (1,1, 5, 30);
INSERT INTO T VALUES (1,1, 6, ‘29);
INSERT INTO T VALUES (3,2, 7, 34);
INSERT INTO T VALUES (3,2, 8, 33);
INSERT INTO T VALUES (3,2, 9, 56);
INSERT INTO T VALUES (1,1, 7, 90);
INSERT INTO T VALUES (2,2, 8, 0,);
INSERT INTO T VALUES (1,1, 8, 80);
INSERT INTO T VALUES (2,2, 8, 65);
INSERT INTO T VALUES (1,1, 9, ‘34);
INSERT INTO T VALUES (2,2, 9, 11);each student belongs to one class_id. each student participates in many quizes. each quiz has its unique id. each student can appear once in a quiz_id
I am doing the below analysis and query:
1. with below query I am finding which student_id had most marks in any 3 successive quizes (see the 3-1 part below) in the query..
SELECT QUIZ_ID,
      STUDENT_ID,
SUM (MARKS) OVER (PARTITION BY STUDENT_ID ORDER BY QUIZ_ID1
RANGE BETWEEN CURRENT ROW AND (3-1) FOLLOWING) consecMARKS , MARKS   FROM
      (SELECT QUIZ_ID,
        STUDENT_ID,
        MARKS,
      ROW_NUMBER() OVER (PARTITION BY STUDENT_ID ORDER BY T.QUIZ_ID) QUIZ_ID1
      FROM T
      WHERE MARKS IS NOT NULL
      ORDER BY 1
    ORDER BY 3 DESC
SQL> /
   QUIZ_ID STUDENT_ID CONSECMARKS
         7          1         170
         6          1         166
         8          1         129
         5          1         106
         8          3          89
         8          2          76
         3          3          68
         7          3          67
         8          2          65
         1          1          60
         9          3          56
   QUIZ_ID STUDENT_ID CONSECMARKS
         9          1          49
         2          2          40
         4          1          40
         9          2          11
15 rows selected.With above query, I can play around and find for any 'n' number of consecutive quizes, like marks in 2 consecutives quizes, 3, 4 and so on but for each 'n' value I've to run a seperate query mentioning (2-1) or (3-1) or (4-1) and so on..
since my table is big and there are about 400 quizes so what I want to find out is for each 'n' consecutive quiz (from 1 to 400) which student had most marks for each consecutie 'n' quiz. Like in 1 (consecutive) quiz which student had the highest marks and then 2 conseuctive quiz who had most marks and then in 3 consecutive quiz who had most marks and so on till 400 consecutive quiz who had most marks... rather than running query for each 'n' value seperately i want a single query that can give me a summary of most marks in each n consecutive quizes...
my sample output is:
Nth consecutive quiz     student_id    sum(marks)
1                        1              90
2                        1              170
3                        1              246
4
100
200
300
400                      ?              ?   Is this possible to get the above output from one single query? If there are two or more students with equal most marks for any 'n' conseutive quizes then both should come in the summary.
Hope I have been able to put up my question clearly.
regards
Ramis

The replay answer
Advertisement
Something like:
SELECT  N,
        QUIZ_ID,
        STUDENT_ID,
        SUM(MARKS) OVER (PARTITION BY N,STUDENT_ID ORDER BY QUIZ_ID1 RANGE BETWEEN CURRENT ROW AND (N-1) FOLLOWING) consecMARKS,
        MARKS
  FROM  (SELECT  QUIZ_ID,
                 STUDENT_ID,
                 MARKS,
                 ROW_NUMBER() OVER (PARTITION BY STUDENT_ID ORDER BY T.QUIZ_ID) QUIZ_ID1
           FROM  T
           WHERE MARKS IS NOT NULL
         SELECT  LEVEL N
           FROM  DUAL
           CONNECT BY LEVEL <= (
                                SELECT  COUNT(DISTINCT QUIZ_ID)
                                  FROM  T
    ORDER BY N,
             consecMARKS DESC
         N    QUIZ_ID STUDENT_ID CONSECMARKS      MARKS
         1          7          1          90         90
         1          8          1          80         80
         1          8          2          65         65
         1          9          3          56         56
         1          1          1          50         50
         1          2          2          40         40
         1          9          1          34         34
         1          7          3          34         34
         1          3          3          34         34
         1          8          3          33         33
         1          5          1          30         30
         N    QUIZ_ID STUDENT_ID CONSECMARKS      MARKS
         1          6          1          29         29
         1          9          2          11         11
         1          4          1          10         10
         1          8          2           0          0
         2          7          1         170         90
         2          6          1         119         29
         2          8          1         114         80
         2          8          3          89         33
         2          8          2          76         65
         2          3          3          68         34
         2          7          3          67         34
         N    QUIZ_ID STUDENT_ID CONSECMARKS      MARKS
         2          8          2          65          0
         2          1          1          60         50
         2          5          1          59         30
         2          9          3          56         56
         2          2          2          40         40
         2          4          1          40         10
         2          9          1          34         34
         2          9          2          11         11
         3          7          1         204         90
         3          6          1         199         29
         3          5          1         149         30
         N    QUIZ_ID STUDENT_ID CONSECMARKS      MARKS
         3          7          3         123         34
         3          8          1         114         80
         3          2          2         105         40
         3          3          3         101         34
         3          1          1          90         50
         3          8          3          89         33
         3          8          2          76         65
         3          8          2          76          0
         3          4          1          69         10
         3          9          3          56         56
         3          9          1          34         34
         N    QUIZ_ID STUDENT_ID CONSECMARKS      MARKS
         3          9          2          11         11
         4          6          1         233         29
         4          5          1         229         30
         4          7          1         204         90
         4          4          1         159         10
         4          3          3         157         34
         4          7          3         123         34
         4          1          1         119         50
         4          2          2         116         40
         4          8          1         114         80
         4          8          3          89         33
         N    QUIZ_ID STUDENT_ID CONSECMARKS      MARKS
         4          8          2          76          0
         4          8          2          76         65
         4          9          3          56         56
         4          9          1          34         34
         4          9          2          11         11
         5          5          1         263         30
         5          4          1         239         10
         5          6          1         233         29
         5          1          1         209         50
         5          7          1         204         90
         5          3          3         157         34
         N    QUIZ_ID STUDENT_ID CONSECMARKS      MARKS
         5          7          3         123         34
         5          2          2         116         40
         5          8          1         114         80
         5          8          3          89         33
         5          8          2          76          0
         5          8          2          76         65
         5          9          3          56         56
         5          9          1          34         34
         5          9          2          11         11
         6          1          1         289         50
         6          4          1         273         10
         N    QUIZ_ID STUDENT_ID CONSECMARKS      MARKS
         6          5          1         263         30
         6          6          1         233         29
         6          7          1         204         90
         6          3          3         157         34
         6          7          3         123         34
         6          2          2         116         40
         6          8          1         114         80
         6          8          3          89         33
         6          8          2          76          0
         6          8          2          76         65
         6          9          3          56         56
         N    QUIZ_ID STUDENT_ID CONSECMARKS      MARKS
         6          9          1          34         34
         6          9          2          11         11
         7          1          1         323         50
         7          4          1         273         10
         7          5          1         263         30
         7          6          1         233         29
         7          7          1         204         90
         7          3          3         157         34
         7          7          3         123         34
         7          2          2         116         40
         7          8          1         114         80
         N    QUIZ_ID STUDENT_ID CONSECMARKS      MARKS
         7          8          3          89         33
         7          8          2          76          0
         7          8          2          76         65
         7          9          3          56         56
         7          9          1          34         34
         7          9          2          11         11
         8          1          1         323         50
         8          4          1         273         10
         8          5          1         263         30
         8          6          1         233         29
         8          7          1         204         90
         N    QUIZ_ID STUDENT_ID CONSECMARKS      MARKS
         8          3          3         157         34
         8          7          3         123         34
         8          2          2         116         40
         8          8          1         114         80
         8          8          3          89         33
         8          8          2          76          0
         8          8          2          76         65
         8          9          3          56         56
         8          9          1          34         34
         8          9          2          11         11
         9          1          1         323         50
         N    QUIZ_ID STUDENT_ID CONSECMARKS      MARKS
         9          4          1         273         10
         9          5          1         263         30
         9          6          1         233         29
         9          7          1         204         90
         9          3          3         157         34
         9          7          3         123         34
         9          2          2         116         40
         9          8          1         114         80
         9          8          3          89         33
         9          8          2          76          0
         9          8          2          76         65
         N    QUIZ_ID STUDENT_ID CONSECMARKS      MARKS
         9          9          3          56         56
         9          9          1          34         34
         9          9          2          11         11
135 rows selected.
SQL> SY.

Go to See the other 5 answers

All selected 'n' consecutive rows in retrieved in a single query

Category:DefaultRelease time:2015-10-11Views:130

hello, I have table T with 50,000 rows create table T (student_id number, class_id number, quiz_id number, marks number) some sample rows like INSERT INTO T VALUES (1,1, 1, 50); INSERT INTO T VALUES (2,2, 2, 40); INSERT INTO T VALUES (3,1, 3, 34); IN[More]

Select ... in (select ....) retrieves all rows

Category:DefaultRelease time:-0001-11-30Views:130

Hi guys, I have the following situation: Table_A ID NUMBER NAME VARCHAR2(10) TABLE_B ID_B NAME_B VARCHAR2(10) By mistake I did the following query: Select * from table_a where id in (select ID from table_b); My question is: Why the query return all r[More]

Selecting only one row at a time

Category:DefaultRelease time:2015-10-11Views:130

Hi experts, i have following doubt regarding selecting rows from a db: Is there any way of selecting only one row AT A TIME from a dabase just to collect the data in rows instead of in a unique document containing all the rows? I would like you to el[More]

Identifying and grouping consecutive rows in sql

Category:DefaultRelease time:-0001-11-30Views:130

I have following data set: CREATE TABLE APPS.T1   ROW_NUM               NUMBER,   EFFECTIVE_START_DATE  DATE                    NOT NULL,   EFFECTIVE_END_DATE    DATE                    NOT NULL,   STATUS                VARCHAR2(30 BYTE) SET DEFINE O[More]

Calculate date differences in consecutive rows and generate a sequence

Category:DefaultRelease time:-0001-11-30Views:130

Hi Guys, I am trying to implement one scenario where in i need to compare the date differences for consecutive rows and generate a sequence against that. this is the table schema: create table temp id int identity(1,1), emp_id int, time datetime inse[More]

How do I select non consecutive columns

Category:DefaultRelease time:-0001-11-30Views:130

I have been working in numbers and Excel and need to select some non-consecutive rows or columns and just can't figure out how to do it. I know how in Windows. Any thoughts? Thanks, MaggieAfter the initial selection, command-click on each additional[More]

Diffrence b/w select single & select upto one row

Category:DefaultRelease time:-0001-11-30Views:130

hi    wat is the diffrence b/w select single & select upto one row? deepakHi, Select single has to be used with a where condition that has all the key fields: It will always return a unique record(If a match is found). Select upto 1 rows would get yo[More]

Difference between select single * & select upto 1 rows

Category:DefaultRelease time:-0001-11-30Views:130

difference between select single * & select upto 1 rowsHi,    According to SAP Performance course the SELECT UP TO 1 ROWS is faster than SELECT SINGLE because you are not using all the primary key fields. Select single is a construct designed to read[More]

Select Single Vs Select upto 1 row

Category:DefaultRelease time:-0001-11-30Views:130

Hi All, Please tell me which of the two statements is better if we want to retrieve only one record from a table. Select single or Select upto 1 rows? Regards, SaurabhThere is a lot of confusion in the replies and also in the other thread. If yoou as[More]

Select single * & Select  * upto one row

Category:DefaultRelease time:-0001-11-30Views:130

What is the difference between Select single * & Select * upto one row?Performance wise which is a better one?Hi,     According to SAP Performance course the SELECT UP TO 1 ROWS is faster than SELECT SINGLE because you are not using all the primary k[More]

Classic report error when no rows to retrieve from DB

Category:DefaultRelease time:-0001-11-30Views:130

Apex 4.0, 11g Should be a quick setting change but can't seem to find it... I have a classic report that SELECT x, y, z FROM table_name WHERE DOC_TYPE_CD = :P25_FILE_TYPE The :P25_FILE_TYPE is filled in by a select list that submits (no validations).[More]

Hot
I'm going to be doing a lot of work for a pretty straight-forward interview type TV show that is looking to spice up its transitions and are open to the idea of purchasing some to go beyond what FCP includes. I was looking at the ones from Eiperle an [More]
I upgraded to 11.1.32 today in hopes that it would fix my issue, but no dice. I run a win8 laptop and iTunes 11.1.2.32. I am accessing a shared library on a NetGear Stora NAS device. Music works fine, but when I try to view one of my videos the iTune [More]
1)I would like to create an intermediate status between 20 and 30 at the approval process. For that I have created another field in the customer include table CATSDB to fill when the user press the approval button. Thus I need a user exit at the 'USE [More]
Hello, My Problem: By client-certificate-based authentication the first step is to prove "Does user�s public key validate user�s digital signature?". How can I prove this on the ServerSide manually, resp. I want to verify it with java classes on [More]
Hi all, I've created a new function module which calls the SAP standard function module SO_NEW_DOCUMENT_ATT_SEND_API1. The mail is sent to the correct receiver. <b>The problem:</b> The message field in all my mails is empty. Only subject field [More]
I've just noticed that on many sites where I would normally see Helvetica Bold for headlines, etc. Safari is now using some form of Helvetica Bold Condensed instead. I've tried toggling some things in Font Book and trashing my Safari plist, but nothi [More]
Can we download themes/skins on nokia 2730c for the default music player...if yes, whereHi Law_Snoweazie, Have you checked what's available in the Nokia Store? Point your web browser to http://store.ovi.com on your phone to download the latest versio [More]
I'm hoping to use Apple TV to replace my cable provider (Comcast). However, some of my "must have" programing requires having an active TV subscription with the content. For example, to get my sports via WatchESPN on Apple TV I have to have a Ca [More]
I need help creating a table dynamically. i have a view in one database. i would like to create a table in another instance using the view structure of a view which is in another instance. the problem i am having is that some columns in the view are [More]
HI, I am facing a very basic problem in using LabVIEW's modulation and advance toolkits in Speedy 33.  When I open Speedy 33 environment, I am unable to find modulation toolkit on it although this toolkit is available in normal LabVIEW environment. W [More]