Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2011
    Posts
    4

    Red face Unanswered: Sorting in SQL 2005

    Dear All!!
    I have three tables (Student, Books, And Grades)
    each student has one book and in the book i have many grades.
    the book table has a start number and an end number; and the grades table has a serial number
    example:

    -This is a description of the "grades" table:

    Grades-ID | Book-ID | Serial-Number | Text

    100 | 1 | 1 | text

    101 | 1 | 2 | text

    102 | 1 | 3 | text

    103 | 1 | 4 | text

    104 | 1 | 5 | text

    105 | 1 | 6 | text

    106 | 1 | 7 | text

    107 | 2 | 8 | text

    108 | 2 | 9 | text

    109 | 2 | 10 | text

    110 | 2 | 11 | text

    111 | 2 | 12 | text

    112 | 2 | 13 | text

    113 | 2 | 14 | text

    114 | 3 | 15 | text

    115 | 3 | 16 | text

    116 | 3 | 17 | text

    117 | 3 | 18 | text

    118 | 3 | 19 | text

    119 | 3 | 20 | text

    120 | 3 | 21 | text

    121 | 4 | 22 | text

    122 | 4 | 23 | text

    123 | 4 | 24 | text

    124 | 4 | 25 | text

    125 | 4 | 26 | text

    126 | 4 | 27 | text

    127 | 4 | 28 | text

    -This is a description of the"Books" table:

    Book-ID | Student-ID | Start-Number | End-Number| (start number is the number of the first page)

    1 | 10452 | 1 | 7

    2 | 10453 | 8 | 14

    3 | 10454 | 15 | 21

    4 | 10455 | 22 | 28

    As you can see the serial numbers of the "Grades" table are the numbers between the start number and the end number of the Books table

    -Description of the "Student" table

    Student-ID | Name | L-Name

    10452 | A | -----

    10453 | B | -----

    10454 | C | -----

    10455 | D | -----

    i want to write an sql query to get the results as following:

    Name Serial Number
    A 1
    B 8
    C 15
    D 22
    A 2
    B 9
    C 16
    D 23
    A 3
    B 10
    C 17
    D 24
    A 4
    B 11
    C 18
    D 25
    A 5
    B 12
    C 19
    D 26
    A 6
    B 13
    C 20
    D 27
    Last edited by cfm 1990; 10-06-11 at 06:03.

  2. #2
    Join Date
    Sep 2011
    Location
    sweden
    Posts
    5

    its simple

    Just study in W3 schools and you can write your own code don't waste your time in copying please

  3. #3
    Join Date
    Sep 2011
    Posts
    31
    This is a simple join.
    Code:
    select s.name, g.[serial-number] from student s inner join books b
    on s.[student-id]=b.[student-id] inner join grades g
    on g.[book-id]=b.[book-id]

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Getting the required result set is indeed easy. Ordering it in the way you specified... What is the logic / business rule behind the way you ordered those records?

    Can you tell why "A 2" should follow "D 22" and not "A 1"? And why "B 9" should follow "A 2" and not "B 8"? ...

    Records in a relational database are by definition unordered. If you want a certain order in the result set, you must specify one (or more) columns that can be used for the ordering.

    A 1
    B 8
    C 15
    D 22
    A 2
    B 9
    C 16
    ...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Oct 2011
    Posts
    4

    Answer

    Dear Wim ;
    I need to sort these three tables in order to print just four grades on each paper :
    so if i print A 1 - B 8 - C 15 - D 21 on the first page;
    the second page must be A 2 - B 9 - C 16 - D 22;
    this procedure must continue to the end of the grades for each Book:
    => in the example i have 7 grades in each book.

    BEST REGARDS

  6. #6
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Use order by asc or order by desc

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by cfm 1990 View Post
    => in the example i have 7 grades in each book.
    Try this:
    Code:
    With CTE
    AS
    (select s.name, 
    	G.SerialNumber,
    	ROW_NUMBER() OVER (PARTITION BY s.name ORDER BY G.SerialNumber asc) as RowNum
    from #Books as B
    		INNER JOIN #Grades as G ON
    			 B.BookID = G.BookID
    		INNER JOIN #Student as S On
    			B.StudentID = s.StudentID
    )
    SELECT Name, SerialNumber
    FROM CTE as T1
    ORDER BY RowNum , Name, SerialNumber
    I don't fully understand your situation or the data design is flawed.
    To get the required output I had to rely on the student.name. That is very suspicious. My query delivers the required output, but more by luck then by determination, I think.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Sep 2011
    Posts
    71

    to generate 4 recordes per page

    Hello cfm 1990,

    As i understand you ,you need to restricted only 4 records per page ,To do that you need reporting tools as Krystal report or sql server report ,
    So i though you cannot control this issue using sql query itself
    thanks

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jassi.singh View Post
    Use order by asc or order by desc
    just another example of totally useless advice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2011
    Posts
    4

    Smile almost the answer

    Dear Wim!
    Your answer is working only if i have 4 students as in the example;
    but if i have more than 4 ; i could not group them 4 by 4
    example if i have customers :A- B-C- D- E- F- G-H
    I want to group A-B-C-D from the start number to the end number of each one and then E- F- G-H

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by cfm 1990 View Post
    Dear Wim!
    Your answer is working only if i have 4 students as in the example;
    but if i have more than 4 ; i could not group them 4 by 4
    example if i have customers :A- B-C- D- E- F- G-H
    I want to group A-B-C-D from the start number to the end number of each one and then E- F- G-H
    The solution delivers the result given in the example. You are now giving new specifications.

    This is a reporting / display problem, not in the first place an SQL problem. The SQL script is really easy.

    I repeat that I don't fully understand the given situation. I am more and more convinced that the data design is flawed.
    The fact that I had to rely on the student.name to display the grades in the right order, is very suspicious.

    As Paultech suggested, perhaps you can solve this with Reporting Services, something I know close to nothing about.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    You can use CTE or inner join with group by clause to accomplish this

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    jassi, please just stop it

    your one-liners are not at all helpful

    if you have a solution, please post it

    otherwise, please stop cluttering these threads with crap

    besides, your latest advice contradicts your earlier advice in post #6

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Oct 2011
    Posts
    4

    Smile The best answer

    Dear all this is the answer:
    but now my answer is how to optimize it



    ALTER procedure [dbo].[SortGrades]
    as
    DECLARE @COUNTER int
    set @COUNTER = 1;
    DECLARE @GradesNumber int
    set @GradesNumber = (select Count (*) from Grades)



    WHILE(@COUNTER <= @GradesNumber )
    BEGIN
    With CTE
    AS
    (select
    o.NameEn,
    c.SerialNo,
    ROW_NUMBER() OVER (PARTITION BY o.NameEn ORDER BY c.SerialNo asc) as RowNum,
    Row_Number() OVER (ORDER BY SerialNo) as rowid
    from Book as B
    INNER JOIN Grades as c ON
    B.BookID = c.BookID
    INNER JOIN Students as o On
    B.StudentId = o.StudentId
    )

    SELECT
    NameEn, SerialNo,,RowNum, rowid
    FROM CTE as T1
    WHERE rowid < @COUNTER +100
    AND rowid >= @COUNTER
    order BY RowNum, SerialNo
    set @COUNTER = @COUNTER + 100
    END



    Thank You

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •