Page 1 of 2 12 LastLast
Results 1 to 15 of 26

Thread: first 7 rows

  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unanswered: first 7 rows

    I have a table sal with the following structure

    emp_no int,
    dept_no int,
    basic_salary money

    Is it possible to extract 7 highly paid employed with one SQL statement?

    any help will be highly appreciated.
    mr_roomi

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select TOP 7
    emp_no,
    dept_no,
    basic_salary
    from YourTable
    order by basic_salary desc
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    top 7

    how will it calculate highest salary? I mean how system knows to display record based on salary field?
    mr_roomi

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "order by basic_salary desc"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    the top clause retrieves the rows that would exist at the "Top" of your result set so if your result set was
    joe
    steve
    bill
    rob
    mary

    and you selected the top 3
    you would get
    joe
    steve
    bill

    the control factor with the top clause is the order by clause
    the order by clause sorts the result set either in ascending (ASC) or descending (DESC) order. so if you sorted a salary column asc, the lowest salary would be at the top correct?. and if you selected the top 7 salaries in that example, you would have the 7 lowest salaries.
    by sorting the salary column in desc order, you would get the top 7 salaries

    please open and read this help file
    Books Online{Limiting Result Sets Using TOP and PERCENT}

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Mr_roomi
    how will it calculate highest salary? I mean how system knows to display record based on salary field?

    What is the context of the salary table? Does it hold weekly salary data?

    Everyone here thinks it's annual.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Sheesh!

    Code:
    create table sal
    (
    emp_no int 
    ,dept_no int
    ,basic_salary money
    )
    
    insert into sal values (1 ,10,1000.00)
    insert into sal values (2 ,10,2000.00)
    insert into sal values (3 ,10,1500.00)
    insert into sal values (4 ,10,1200.00)
    insert into sal values (5 ,10,1000.00)
    insert into sal values (6 ,10,3000.00)
    insert into sal values (7 ,10,2200.00)
    insert into sal values (8 ,10,1250.00)
    insert into sal values (9 ,10,1350.00)
    insert into sal values (10 ,10,1000.00)
    
    select * from sal
    
    select top 7 emp_no, dept_no
    ,Basic_Salary as 'WeeklySalary' --by week
    ,BiWeeklySalary=(Basic_Salary*2) --BI week
    ,AnnualSalary=((basic_salary*2)*26) --Annual
    from sal
    order by AnnualSalary desc

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...so what if there's more than 1 salary row per employee?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...an aggregate query to sum up the salary values.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    ...an aggregate query to sum up the salary values.

    Thanks you...my point...

    Without the DDL of the table we'd be just shooting in the dark

    Bang

    Yo blind dude..did that hit you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Nyah nyah, ya missed me!

    You forget that, as the Blindman, I shoot in the dark just fine!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    okay
    since the poster hasnt said anything about it then this post is officially dead.

    dont be a malingerer.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "since the poster hasnt said anything about it then this post is officially dead"???

    Like THAT has ever stopped us before...

    and "malingerer"?

    In the words of Inigo Montoya -
    "You keep using that word. I do not think it means what you think it means."

    http://dictionary.reference.com/search?q=malingerer
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you killed my father -- prepare to die!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i know exactly what it means.
    you are spending your time posting to a thread that is dead
    so you are in effect acting crazy(sick) to get out of the real work here
    and that is driving rdjabarov and pat phelan crazy.

Posting Permissions

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