Professional Web Applications Themes

How to use distinct - MySQL

Hey There, I had a question about the use of 'DISTINCT'; I have a table with address information with the following info; Street - City - Province - HitsMonth - HitsTotal Now i want to create a top 10 list, ordered by HitsMonth (most requested info this moth). This is off course not a real problem, the following works fine. SELECT * FROM $mysql_table ORDER BY (hitsmonth+0) DESC LIMIT 0,10 This provides me of the following list Province - City - Hits Zuid-Holland - Den Haag - 92 Zuid-Holland - Den Haag - 87 Brabant - Helmond - 20 Brabant ...

  1. #1

    Default How to use distinct

    Hey There,

    I had a question about the use of 'DISTINCT'; I have a table with
    address information with the following info; Street - City - Province
    - HitsMonth - HitsTotal

    Now i want to create a top 10 list, ordered by HitsMonth (most
    requested info this moth). This is off course not a real problem, the
    following works fine.

    SELECT * FROM $mysql_table ORDER BY (hitsmonth+0) DESC LIMIT 0,10

    This provides me of the following list
    Province - City - Hits
    Zuid-Holland - Den Haag - 92
    Zuid-Holland - Den Haag - 87
    Brabant - Helmond - 20
    Brabant - Helmond - 20

    Now off course it's not cool to have double city's so I use distinct
    SELECT DISTINCT province, city FROM $mysql_table ORDER BY (hitsmonth
    +0) DESC LIMIT 0,10

    This provides me ow the following list
    Zuid-Holland - Den Haag
    Brabant - Helmond
    Brabant - Best
    Brabant - Breda
    Brabant - Zundert
    Brabant - Eindhoven
    Brabant - Etten-leur

    I don't like this list either, now I want that each province is unique
    in the list bud I can't get this to work for me. With the query below
    I only select province, so city isn't displayed. Is there a way to
    DISTINCT province and select still select the city at the same time?

    SELECT DISTINCT province FROM $mysql_table ORDER BY (hitsmonth+0)
    DESC LIMIT 0,10

    Good list, only the sity is missing :(

    Noord-Holland -
    Gelderland -
    Zuid-Holland -
    Overijssel -
    Flevoland -
    Utrecht -
    Groningen -
    Limburg -
    Drenthe -
    Brabant -

    Thanks in advance

    BPG Guest

  2. #2

    Default Re: How to use distinct

    On 23 Jul, 11:03, BPG <nu> wrote: 

    What you have said seems to make no sense!
    If a province can contain multiple cities, then if you want all the
    cities displayed, the provinces must be displayed multiple times?

    Also, since you can have different numbers of hits for the same
    province/city combination, how do you decide which city has the
    greatest number of hits?

    Captain Guest

  3. #3

    Default Re: How to use distinct

    Thanks for your reaction, and you're right, I'm not so good in
    explaining things!

    I have the following fields in my table :
    locatieid, province, city, location, astreet, hmonth, htotal

    For every new location, I create a new record in my database example:

    id Province City Location
    1 Zuid-Holland Den Haag Center
    2 Zuid-Holland Den Haag Cental Station
    3 Zuid-Holland Rotterdam Center

    When a user opens a page with the info for Den Haag, all the locations
    are shown.
    Witch 'location' has highest number of hits doesn't really matter
    because i'll create my list with only Province - City

    The thing is, I could show a list with Province - City info ordered by
    hits, only this means that one province could be in the list multiple
    times, and that is what I want to avoid.

    So I want to create a list Showing "Province - City", where the
    province is Unique (DISTINCT province) and the City shown next to
    'province' is the city with the most hits within this province, ORDER
    where the Province with the most viewed city has to be on top!

    Example, where the city with the most hits in a province is on top,
    and only one city per province is shown! (A province is not listed
    more than once)

    Noord-Holland - Amsterdam (200)
    Gelderland - Apeldorn (180)
    Zuid-Holland - Den Haag (150)
    Overijssel - Almelo (100)
    Flevoland - Leylystad (30)
    Utrecht - Heuvelrug (10)
    Groningen - Appingedam (7)

    I hope a made my self more clear this time, thanks in advance

    BPG Guest

  4. #4

    Default Re: How to use distinct

    On 23 Jul, 13:08, BPG <nu> wrote: 

    Before I try to craft the solution, can you explain how in the table:

    Province - City - Hits
    Zuid-Holland - Den Haag - 92
    Zuid-Holland - Den Haag - 87
    Brabant - Helmond - 20
    Brabant - Helmond - 20

    "Zuid-Holland - Den Haag" has 92 hits and also has 87 hits?

    Captain Guest

  5. #5

    Default Re: How to use distinct

    Yes I can, these locations are connected with a table named 'dates'.
    In this table all kinds of event's +dates are logged, party's etc.
    When a user submits a party, he/she selects the location where that
    party will be. Most city's have more than one party location.

    So a user can search a number of events, party's etc and when the
    event is opened it update's the corresponding location + 1.

    Now what I want to do with my top10 list, is to link to the 'overview'
    page of a city, showing all the locations, so that it doesn't matter
    witch location within a city has the most hits. the 'province - city'
    witch has the most viewed location just has to be on top and in order
    not to show only party places of one province I want to DISTINCT
    province so that only the city with the top party location in province
    is showed

    List example, each location is connected to one or more events

    Province - City - location - hits
    Zuid-Holland - Den Haag - Station - 100
    Zuid-Holland - Den Haag - City Center - 99
    Brabant - Helmond - High School - 250
    Brabant - Helmond - Club 7 - 299
    Noord Holland - Amsterdam - Shiphol - 300

    This list should be sorted:
    Noord Holland - Amsterdam
    Brabant - Helmond
    Zuid-Holland - Den Haag

    BPG Guest

  6. #6

    Default Re: How to use distinct

    On 23 Jul, 14:22, BPG <nu> wrote: 

    OK, got it now.

    Anyway, as so often in this forum, the answer to your question is to
    use the "Strawberry Query". DISTINCT and GROUP BY do not come into it
    at all.

    See the most recent discussion of it here:

    http://groups.google.co.uk/group/comp.databases.mysql/browse_frm/thread/8bd51cd2c27bc1e7/#

    Captain Guest

  7. #7

    Default Re: How to use distinct

    Thanks for your answer again :)

    I searched for the "Strawberry Query" in both this group and in
    google, i can't hardly find anything about it, What is did find was
    selecting information from tables using left join, that is what i use
    to 'connect' both the date and the location table, unfortinitly i cant
    find any examples on the Strawberry Query in the way i probably need
    it (selecting information from one table)

    Do you maybe know where to find a tutorial on the subject ?

    thanks in advance

    BPG Guest

  8. #8

    Default Re: How to use distinct

    BPG wrote: 

    I posted a link to the post that I made yesterday that contained the words
    "Strawberry Query".

    So not only are you unable to use Google to search, you can't even use your
    eyes to read the specific post that I gave you a link to!

    I give up!


    Paul Guest

  9. #9

    Default Re: How to use distinct

    Hey man, I did follow the link, and I did read your post..
    I'm just not a pro (jet ;p), so I wanted some more examples where the
    "Strawberry Query" is used.

    Searching on Google for -MYSQL tutorial "Strawberry Query"-
    Didn't help me, so maybe I am unable to use Google to search, or may
    there isn't to much to find on the subject!

    Anyway I won't give up, thanks for the help (otherwise I wouldn't even
    have known something like a "Strawberry Query" existed!

    BPG Guest

  10. #10

    Default Re: How to use distinct

    On 24 Jul, 13:53, BPG <nu> wrote: 

    You won't find a tutorial on "Strawberry Query". If you had read the
    thread that I posted a link to you would know why it is called the
    Strawberry Query and you would also have seen the link to the MySQL
    manual from where it originated.

    Captain Guest

Similar Threads

  1. using distinct
    By keller18 in forum Coldfusion - Getting Started
    Replies: 6
    Last Post: May 16th, 04:39 PM
  2. need help on DISTINCT
    By Krechting in forum ASP Database
    Replies: 1
    Last Post: January 12th, 01:07 AM
  3. Replies: 3
    Last Post: April 18th, 12:52 PM
  4. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139