Results 1 to 4 of 4

Thread: How To select many fields by DISTINCT one field ?

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    syria
    Posts
    854

    Question How To select many fields by DISTINCT one field ?

    I am usimg vb2008 Ms Access2007
    I have four fields : Name,Tel,City,Job
    I want to select all fields by "DISTINCT City"

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: How To select many fields by DISTINCT one field ?

    I'm sure you have some idea of what you mean, but it isn't clear to us (with this kind of question, there are hundreds of variations of what people mean)

    Assuming data like the following:
    Code:
    Name   Telephone  City        Job
    Joe    234        London      Baker
    John   1321       London      Builder
    Dave   3165       London      Soldier
    Eric   456        Liverpool   Baker
    Tom    4546       Liverpool   Pilot
    Please show us how you want this data displayed (and preferably explain why the output is as you show).

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    syria
    Posts
    854

    Re: How To select many fields by DISTINCT one field ?

    I got it
    I can use DISTICNT only for one field if use it without another field
    but if i want to use it with many fields i will use it with group by example

  4. #4
    Lively Member
    Join Date
    Jan 2020
    Posts
    120

    Re: How To select many fields by DISTINCT one field ?

    Hello,@nader

    Please try this code, How To select many fields by DISTINCT one field ?

    Code:
    SELECT * FROM
    GPSReport AS G1
    JOIN (SELECT device_serial, max(datetime) as mostrecent 
          FROM GPSReport group by device_serial) AS G2
    ON G2.device_serial = G1.device_serial and g2.mostrecent = g1.datetime
    ORDER BY G1.device_serial
    I hope this code will be useful.
    Thank you.

Posting Permissions

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



Click Here to Expand Forum to Full Width