Post reply

SQL Query

  • June 23, 2009 at 12:02 pm

    #95809

    I need to retrive first 5 higest salary of dept = 10 & dept = 20

    so the result set should contain total of 10 rows as result (5 from dept = 10 & 5 from dept = 20)

    The table looks like this

    enum dept sal

    1 10 1000

    2 10 35435

    3 10 584732

    4 10 55312

    5 10 5468425

    6 10 81325

    7 10 56421

    8 10 3545

    9 10 3548

    10 10 5642

    11 20 351312

    12 20 3545332

    13 20 5656432

    14 20 234568

    15 20 3546

    16 20 25123685

    17 20 2315

    18 20 251235

    19 20 32534

    2) And query to find the Nth value from a column for instance in the Employee table under the column EmployeeName you have the records

    EmployeeName

    George

    Michael

    Tom

    Evans

    Brad

    Rudy

    You do not exactly know the records this field contains you need to write a query which fetches the Nth value from that field and retrieves the result set.

    Thanks

  • June 23, 2009 at 12:10 pm

    #1014160

    Please follow the link in my signature on how to post sample data.

    This should include what you've tried so far.

    Please help us help you.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 23, 2009 at 12:21 pm

    #1014167

    Wrong I may be but class work this seems to me.

    Before we show you how we would accomplish these tasks, please provide us with what you have tried so far and where you are having difficulties with your code.

  • June 23, 2009 at 12:46 pm

    #1014178

    Please find the table and the data below:

    Create Table Employee

    (

    Enum int Primary Key,

    Dept nvarchar(15),

    Sal money

    )

    INSERT INTO Employee (Enum, Dept, Sal)

    SELECT '1', '10', '1000' UNION ALL

    SELECT '2', '10', '12567' UNION ALL

    SELECT '3', '10', '23876' UNION ALL

    SELECT '4', '10', '4323' UNION ALL

    SELECT '5', '10', '8324' UNION ALL

    SELECT '6', '10', '9874' UNION ALL

    SELECT '7', '10', '8879' UNION ALL

    SELECT '8', '10', '2314' UNION ALL

    SELECT '9', '10', '7763' UNION ALL

    SELECT '10', '10', '9876' UNION ALL

    SELECT '11', '20', '34219' UNION ALL

    SELECT '12', '20', '8342' UNION ALL

    SELECT '13', '20', '27843' UNION ALL

    SELECT '14', '20', '9879' UNION ALL

    SELECT '15', '20', '8891' UNION ALL

    SELECT '16', '20', '7634' UNION ALL

    SELECT '17', '20', '6671' UNION ALL

    SELECT '18', '20', '9198' UNION ALL

    SELECT '19', '20', '8873' UNION ALL

    SELECT '20', '20', '5437' UNION ALL

    Thanks

  • June 23, 2009 at 1:06 pm

    #1014195

    We're getting closer...

    So, what have you tried so far?

    Regarding the 2nd question:

    The answers for any given "N" with 0 < "N" <= [total number of rows in that table] is always "Tom".

    Just using the information you provided it's going to be tough to prove me wrong... 🙂


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 23, 2009 at 1:17 pm

    #1014203

    Hello,

    Select * From [dbo].[Employee] E1 Where

    (N-1) =

    (Select Count(Distinct(E2.[Sal])) From [dbo].[Employee] E2 Where

    E2.[Sal] > E1.[Sal])

    This is what I have but this doesn't solve my purpose I want top 5 each from department 10, 20.

    Regarding the 2nd question I exactly do not know how to put my question forward. Thanks though for replying and correcting my questions. I will try to be more precise with my second question and get back with you.

    Thanks

  • June 23, 2009 at 1:35 pm

    #1014221

    We're almost there...

    in order to get the top 5 per group you need to rank them first. Also, you have to define, how you deal with tie values. Please look in BOL (BooksOnLine = SQL Server help file) for the bold marked key word.

    With your 2nd question you should follow the article I recommended:

    post table definition together with sample data and expected result.

    Also, please include what you've tried so far.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 23, 2009 at 1:36 pm

    #1014223

    You may want to look up the ROW_NUMBER() function and CTE's in Books Online (BOL, the SQL Server Help System). Read these and if you have questions, ask. To help your understanding, here is some code for your first questions. And, again since I seem to be having more issues with posting code directly in the thread, I have atteached my code as a text file.

  • June 23, 2009 at 1:37 pm

    #1014226

    After reading Lutz's post, you can also change the code I provided to use the RANK() or DENSE_RANK() functions. Be sure to read about those in BOL as well.

  • June 24, 2009 at 5:42 pm

    #1014998

    Thanks Lynn and Lutz.

    Regards

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply