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

On 23 Jul, 11:03, BPG <m...@bpg.nu> wrote:
> 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

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?

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

On 23 Jul, 13:08, BPG <m...@bpg.nu> wrote:
> 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

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?

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

On 23 Jul, 14:22, BPG <m...@bpg.nu> wrote:
> 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

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/com...1cd2c27bc1e7/#

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 wrote:
> 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

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!

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!

On 24 Jul, 13:53, BPG <m...@bpg.nu> wrote:
> 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!

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.