Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    17

    Question Unanswered: First function, ordering and grouping

    Hi,

    I have a table where the ID is repeated, but other info is different. I wish to select only one record for each ID in the table, and have some criteria so the correct ones are picked.

    In my attempt to solve it, I first created an ordering query, so that for each ID, the one to pick was at the top of the list. Then I tried a group by query on this subquery, using the first() function to get the top ones.

    Many of you will be ahead of me already - this doesn't work! (it picks the IDs as if there weren't sorted)

    But, from what I could find out, when working on a sorted query, the first() function should work as I want it to! So where's the problem happening??

    Any ideas?
    Cheers,
    Chris

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Try:

    SELECT TOP 1 .... FROM ... WHERE ... ORDER BY ... ASC/DESC;

    ASC/DESC = Ascending or Descending depending on your pleasure ...

  3. #3
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    17
    Originally posted by M Owen
    Try:

    SELECT TOP 1 .... FROM ... WHERE ... ORDER BY ... ASC/DESC;

    ASC/DESC = Ascending or Descending depending on your pleasure ...
    Thanks for the swift response, sorry but I'm not entirely sure where I should do what you're asking.

    I want to display all ID's but just one record for each ID, so a select top 1 would have to be repeated lots of times on different student numbers? Can I work that with a group by clause?

    Cheers again,
    Chris

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ah ... Sorry about that. Jumped the gun ... Try DISTINCT.

    SELECT DISTINCT ....

  5. #5
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    17
    Hey again,

    Sorry but I really need to be talked through this! Where would I use the select distinct? How would this work with picking my preferred row? (i.e, the one nearer the top of the list when sorted by ID and the second value)

    More explanation - my table looks like this:

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

    As you can see, I wanna pick the top one for each ID (either the one they currently work for, otherwise it doesn't matter)

    I thought:

    select ID, first(company) from tableX group by ID

    would work, but it doesn't!

    Cheers,
    Chris

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    DOH! Why not query for WHERE (StillWorksHere='Yes'); ? Are you looking for each ID/Employee and whether they are there or not?

  7. #7
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    17
    Originally posted by M Owen
    DOH! Why not query for WHERE (StillWorksHere='Yes'); ? Are you looking for each ID/Employee and whether they are there or not?
    Hehe, no sorry I'm not explaining myself. I just want every ID to appear in the table - just once. The preference is their current job, but then otherwise any job will do.

    This is why I thought I could order by currentjob, and after grouping by ID just use the first() function.

    I appreciate the help,
    Chris

Posting Permissions

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