first()/order by query

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Not Me

    first()/order by query

    Hi,

    Having a table with some duplicate ID's (different data tho), how can I
    return the list but with only one record from each ID? Would this be using
    the first() function and grouping?

    Also, how would I choose which would be the first to be picked? If I did an
    order by xx, would this alter the results, or is the first record picked
    before the ordering is done?

    Cheers,
    Chris


  • Not Me

    #2
    Re: first()/order by query

    "Not Me" <sum1@sumwhere. fake> wrote in message
    news:bpvo94$3p2 $1@ucsnew1.ncl. ac.uk...[color=blue]
    > Hi,
    >
    > Having a table with some duplicate ID's (different data tho), how can I
    > return the list but with only one record from each ID? Would this be[/color]
    using[color=blue]
    > the first() function and grouping?
    >
    > Also, how would I choose which would be the first to be picked? If I did[/color]
    an[color=blue]
    > order by xx, would this alter the results, or is the first record picked
    > before the ordering is done?[/color]

    To partially answer my own question, It appears the ordering won't help (I
    also had to do this in an intermediate query). If anyone can help please
    do!

    Cheers
    Chris



    Comment

    • Pavel Romashkin

      #3
      Re: first()/order by query

      If you take a look at the Totals row in QBE grid, you will see all sorts
      of aggregate functions to return one value from a group: Firt, Last,
      Max, Min, etc.

      Pavel

      Not Me wrote:[color=blue]
      >
      > Hi,
      >
      > Having a table with some duplicate ID's (different data tho), how can I
      > return the list but with only one record from each ID? Would this be using
      > the first() function and grouping?
      >
      > Also, how would I choose which would be the first to be picked? If I did an
      > order by xx, would this alter the results, or is the first record picked
      > before the ordering is done?
      >
      > Cheers,
      > Chris[/color]

      Comment

      • Not Me

        #4
        Re: first()/order by query

        "Pavel Romashkin" <pavel_romashki n@hotmail.com> wrote in message
        news:3FC3829F.6 CDEFAD6@hotmail .com...[color=blue]
        > If you take a look at the Totals row in QBE grid, you will see all sorts
        > of aggregate functions to return one value from a group: Firt, Last,
        > Max, Min, etc.[/color]

        Ta, I think I need to use the first() function but it isn't working as I
        think it should.

        The sorting query displays results like this (apologies for poor
        formatting!)

        ID Company StillWorksHere
        111 X No
        112 Y Yes
        112 X No
        112 Z No
        113 Y Yes
        113 Z No

        So I thought if I grouped by ID and picked the first company I'd get the 2
        that still work for that company, and the one that doesn't work for any
        company. But I get results no different from if I use the original table
        rather than the sorted one.

        Cheers,
        Chris


        Comment

        • Bryan Christopher

          #5
          Re: first()/order by query

          "Not Me" <sum1@sumwhere. fake> wrote in message news:<bpvo94$3p 2$1@ucsnew1.ncl .ac.uk>...[color=blue]
          > Hi,
          >
          > Having a table with some duplicate ID's (different data tho), how can I
          > return the list but with only one record from each ID? Would this be using
          > the first() function and grouping?
          >
          > Also, how would I choose which would be the first to be picked? If I did an
          > order by xx, would this alter the results, or is the first record picked
          > before the ordering is done?
          >
          > Cheers,
          > Chris[/color]

          Hi Chris, you can use a query that displays only DISTINCT records.
          Meaning, if you have a query that displays only ID, then using
          DISTINCT will only show distinct records, excluding duplicate ID's.
          If you have a query that displays ID and a date or some other field,
          only distinct field combinations will appear...for example, if you
          have once instance of ID=1 and Date =9/4/96, then the combination of
          these fields would produce only one distinct record. If you have a
          second instance of ID=1 with a different Date = 9/5/97, then two
          records will be displayed for ID=1, one with the first date, and the
          other with the second date, since the field combinations of ID and
          Date are distinct for each record. It's kind of hard to explain in
          text.

          To make your query show only distinct records, open your query in
          Design View; under Query Properties, set Unique Values = Yes.

          Comment

          • Not Me

            #6
            Re: first()/order by query

            "Bryan Christopher" <bhelwig@medlin k.com> wrote in message
            news:d7542c61.0 311251329.7fc9b 49c@posting.goo gle.com...[color=blue]
            > "Not Me" <sum1@sumwhere. fake> wrote in message[/color]
            news:<bpvo94$3p 2$1@ucsnew1.ncl .ac.uk>...
            <snip>[color=blue][color=green]
            > > Also, how would I choose which would be the first to be picked? If I[/color][/color]
            did an[color=blue][color=green]
            > > order by xx, would this alter the results, or is the first record picked
            > > before the ordering is done?
            > >
            > > Cheers,
            > > Chris[/color]
            >
            > Hi Chris, you can use a query that displays only DISTINCT records.
            > Meaning, if you have a query that displays only ID, then using
            > DISTINCT will only show distinct records, excluding duplicate ID's.[/color]
            <snip>[color=blue]
            > To make your query show only distinct records, open your query in
            > Design View; under Query Properties, set Unique Values = Yes.[/color]

            Cheers for that,

            What I'm having trouble with is the next part, I'll copy this table from
            another post
            ----------
            ID Company StillWorksHere
            111 X No

            112 Y Yes
            112 X No
            112 Z No

            113 Y Yes
            113 Z No
            ----------

            The above is my table after ordering, you see if I pick each first instance
            of ID I will get a preference of the company that person still works for,
            but if they aren't working at the minute (e.g. ID 111), we still get their
            record, and an example of a job they've done.

            I thought the first function would work in this case, but I can't get it to
            do what I want.

            Cheers,
            Chris


            Comment

            • Phil

              #7
              Re: first()/order by query

              > ----------[color=blue]
              > ID Company StillWorksHere
              > 111 X No
              >
              > 112 Y Yes
              > 112 X No
              > 112 Z No
              >
              > 113 Y Yes
              > 113 Z No
              > ----------[/color]

              I'm with you part of the way with what you are trying to do. If I
              understand this right, firstly you want to find all the people who are
              still working somewhere:

              SELECT * from YourTable where StillWorksHere = yes
              which gives with your data
              ID Company StillWorksHere
              112 Y Yes
              113 Y Yes

              Then you want to pick up the people who worked at one company, but now
              don't. And also don't work anywhere else:

              select YT.ID,* from YourTable as YT where YT.ID not in (select yt.id
              from YourTable as YT2 where YT.id=YT2.id and StillWorksHere =yes)
              which gives with your data
              ID Company StillWorksHere
              111 X No.


              You can union these queries if you like.

              But this still leaves a hole in your results. Lets take a look at
              someone who worked at company Z, moved to company Y and then retired.
              So the table now looks like this:

              ID Company StillWorksHere
              111 X No
              112 Y Yes
              112 X No
              112 Z No
              113 Y Yes
              113 Z No
              114 Z No
              114 Y No

              if I run the unioned query, I get:

              ID Company StillWorksHere
              111 X No
              112 Y Yes
              113 Y Yes
              114 Y No
              114 Z No

              See how 114 turns up twice? This is where I get stuck. How do you
              intend to tell the difference between the last two lines of the table?
              What is it logically that makes one of them right and the other
              wrong? As far as I can tell from your post its the date/time of
              leaving. You want to show the company that was last recently worked
              at. If this is so, then you could attach a date/time field and
              populate it with the now() of the time the person left. This might
              make your problem easier.

              I hope this has been some help to you, if not, please explain again
              your criteria.

              Cheers

              Phil.

              Comment

              • Not Me

                #8
                Re: first()/order by query

                "Phil" <phil@ideastaki ngshape.co.uk> wrote in message
                news:5fd988b0.0 311260845.17b08 efd@posting.goo gle.com...[color=blue][color=green]
                > > ----------
                > > ID Company StillWorksHere
                > > 111 X No
                > >
                > > 112 Y Yes
                > > 112 X No
                > > 112 Z No
                > >
                > > 113 Y Yes
                > > 113 Z No
                > > ----------[/color]
                >
                > I'm with you part of the way with what you are trying to do. If I
                > understand this right, firstly you want to find all the people who are
                > still working somewhere:[/color]

                Hi,

                Thanks for going to all that trouble! However I managed to get it going
                eventually using max and subselect (robbed it from an access website)

                Chers,
                Chris


                Comment

                Working...