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
Hi All, I have used the standard hierarchy type (A) to create a 2(or 3) level hierarchy  (top level node, 2nd level node and sold t party) and the hierarchy assignments look fine at the customer level. I have assigned the hierarchy type to the sales [More]
Hi Experts, I have done following Config & data set up for Internal Settlement (Forwarding Order) while I intent to do Inter Company settlement. (I am using TM9.0 SP01 version) In Movement Type Stage sequence set to "No Rule" for internal Se [More]
Want to move a photo from the last position in an album to the first. Tried cut paste/tried drag/havent found the answer. Thanks, photogrannyIn albums (not smart albums) you simply select the album in the source pane on the left and drag the photos w [More]
hi all, we are facing a problem in planning unit hierarchy in Hyperion planning, where we have 4 users, Hierarchy is define as hierarchy: budget approved version: approved Entity: Finance Senario:Budget haroon asghar ( owner) adeel javid (Reviewer) N [More]
I've seen something like same but it doesn't work for me. I have a table which is created using xml rules, some time it contains empty xml tag also converted into row but I don't want these empty rows. After table creation empty rows need to be delet [More]
In trying to work with data from several PDF files, I subscribed to Adobes's online facility to convert source.pdf to output.docx. Output.docx is missing more than a page of data I need. I don't understand why this is happening. I tried then to conve [More]
I have created several logos in CS4 Illustrator and exported them to into a jpg.  However, when I am creating my website using CS4 Dreamweaver and try and place the image, it comes up as a blank file logo.  I can load other jpgs not created by me so [More]
Hi XI friends, in my file to abap proxy, in sxmb_moni i am getting message like "recorded for outbound processing" and Q status is stopped. please help me. thanks and regards ramHi friends.. Trace is like this <?xml version="1.0" en [More]
hi, I found that logarithmic scale is not supported for line bar combo. I have checked the checkbox "Use logarithmic scale" under "Tick Marks & Scale Type" under "Axis Scaling" for the left axis. but the charts (both chec [More]
Hi all, I'm using jdic web browser (windows version) and customizing it for a project. I want to make an executable jar file. Since I'm using NetBeans 6.7.1, I'm getting a jar file in dist folder. Bt when I run it it does not open the jdic browser. T [More]