Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2008
    Posts
    49

    Unanswered: select first row per group

    Could somebody give me an idea on how to select the first row per group?

    I'm using MS SQL SERVER 2000

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please could you define "first". What data do you use to flag a record as "first"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2008
    Posts
    49
    Ok.. lets just say we i have this table

    businessname address phone
    ------------------------------------------------
    company a address a 111 111-1111
    company a address b 111 111-1111
    company a address a 222 222-2222
    company b address a 333 333-3333
    company b address c 444 333-3333


    I'm suppose to group them by businessname and only get the first row of each group of businessname. So I would have a result like this:

    businessname address phone
    ------------------------------------------------
    company a address a 111 111-1111
    company b address a 333 333-3333


    I hope you understand my explaination. Sorry if I can't explain thoroughly..

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - so if your data was instead:
    Code:
    businessname address phone
    ------------------------------------------------
    company a address b 111 111-1111
    company a address a 222 222-2222
    company a address a 111 111-1111
    company b address c 444 333-3333
    company b address a 333 333-3333
    What would the result be?
    I am going somewhere here - stick with me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2008
    Posts
    49
    I already said it... I should output a result like this:

    businessname address phone
    ------------------------------------------------
    company a address a 111 111-1111
    company b address a 333 333-3333

    I should only get the row (the first one for each group of businessname)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - so even when I jumbled up the data, you still picked out the same two rows. So - how did you know they were first? What identified them as first, even though I moved them to last?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2008
    Posts
    49
    Aaah! Sorry, sorry I made a mistake! This is suppose to be the result:

    businessname address phone
    ------------------------------------------------
    company a address a 111 111-1111
    company b address c 444 333-3333


    At first, I order them by businessname in ascending so all businessname with the same name will be in sequence and their I must figure out how to get the first row of each businessname...

    businessname address phone
    ------------------------------------------------
    company a address b 111 111-1111 <-
    company a address a 222 222-2222
    company a address a 111 111-1111
    company b address c 444 333-3333 <-
    company b address a 333 333-3333

    Do you think what would be the easiest way? Do I still need an unique key ID or something to get the results I need?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry - I've run out of time. Others will pick this up now - they will understand where we have got to.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As poots pointed out, what tells you which row is the "first" in any given group? You seem to want specific rows, but neither poots nor I can tell how you made that choice. In order to pick out just one row, you need to either accept any random row from a group or specify a way to determine which row is the one that you want.

    -PatP

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Correct. Suppose for company a, you now swap the first and second row. Which one would you want then?

    Remember, a database will not return data in any order at all, unless you specify the exact sort order that is required. If you run a query which sorts only on company name today, company a might be returned with the 111 phone number as the first record. You could run the identical query 5 minutes later, and get the 222 phone number first.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  11. #11
    Join Date
    Dec 2008
    Posts
    135
    try this once
    declare @table table(businessname varchar(32), address varchar(32), phone varchar(32))
    insert into @table select 'company a', 'address b', '111 111-1111'
    insert into @table select 'company a', 'address a', '222 222-2222'
    insert into @table select 'company a', 'address a', '111 111-1111'
    insert into @table select 'company b', 'address c', '444 333-3333'
    insert into @table select 'company b', 'address a', '333 333-3333'

    select identity(int,1,1)as rid, * into #temp from @table

    Select M.businessname,M.address,m.phone
    from
    (Select businessname,address,phone,(Select Count(*) from #temp
    Where businessname = Z.businessname and rid <= Z.rid ) as Seq
    from #temp Z)M
    Where M.seq =1

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That's a great thought bklr, but there is no rid or seq column in the original definition of the problem. I can easily create any number of possible "correct" solutions if I can change the problem definition, but that won't help unless I happen to guess right and pick an extension that's compatible with the real world problem.

    -PatP

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by loquin
    Remember, a database will not return data in any order at all, unless you specify the exact sort order that is required.
    Blue Gemini - this is fundamental to relational databases. To expand, one of the principles of a relation (the concept from which we get the name "relational database", and for our purposes here analogous to a table) is that the rows are unordered. There is no such thing as first, last, third from bottom etc. Think of it a bit like a bag (the table) of marbles (the rows). No marble is first or second, but all are stored in the container.

    As lou said, even if your data seems to come back in one order rather than another, this is just conincidence if there is no order by clause. The database just gathers & returns the data in the most efficient order to satisfy the query. This can change depending on other circumstances unless you specicify an order by clause.

    So - given that the order has no meaning, how would you now define first? It can either be "random" (you don't care, you just want one record per group) or based on the value of data in a column or columns.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jun 2008
    Posts
    49
    Hmm, guess I'm just confuse from what I'm saying... I'll try to figure it out a little bit

    Anyway, thank you very much to all of you for the idea

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by BlueGemini
    Do I still need an unique key ID or something to get the results I need?
    The short of it is we either get a single random row, or you do provide some columns that would uniquely tell us exactly which row you want
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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