Mysql Query Question

[YoBro]

Hi,

I have a database that stores in a field the number of views for a product.

I want to write a query that gets the two highest views, so I can then
output the result.

I have this so far, but not sure how to adapt it to get the two highest
views.

mysql_query("SELECT max(views) FROM stock WHERE status='enabled' AND photo1
!= ''");

I then plan to loop through the results and echo the photo value for each
result.

I am sure this is easy, but just can't figure out the correct syntax.

Thanks,

YoBro

[Geoff Berrow]

I noticed that Message-ID: <AO****************@news.xtra.co.nz> from
YoBro contained the following:

I have this so far, but not sure how to adapt it to get the two highest
views.

mysql_query("SELECT max(views) FROM stock WHERE status='enabled' AND photo1
!= ''");

order it by views and simply print the top two records
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Michael S. Clark]

YoBro wrote:

Hi,

I have a database that stores in a field the number of views for a product.

I want to write a query that gets the two highest views, so I can then
output the result.

I have this so far, but not sure how to adapt it to get the two highest
views.

mysql_query("SELECT max(views) FROM stock WHERE status='enabled' AND photo1
!= ''");

I then plan to loop through the results and echo the photo value for each
result.

I am sure this is easy, but just can't figure out the correct syntax.

Thanks,

YoBro

mysql_query("
SELECT MAX(views) AS view_count
FROM stock
WHERE status = 'enabled'
AND photo1 IS NOT NULL
ORDER BY view_count DESC
LIMIT 0,2
");

I think this is correct but I haven't tested it.

Michael.

[Virgil Green]

"YoBro" <yo***@wazzup.co.nz> wrote in message
news:AO****************@news.xtra.co.nz...

Hi,

I have a database that stores in a field the number of views for a product.
I want to write a query that gets the two highest views, so I can then
output the result.

I have this so far, but not sure how to adapt it to get the two highest
views.

mysql_query("SELECT max(views) FROM stock WHERE status='enabled' AND photo1 != ''");

Micheal was very close:
mysql_query("
SELECT *
FROM stock
WHERE status = 'enabled'
AND photo1 IS NOT NULL
ORDER BY views DESC
LIMIT 2
");

You don't want to use the max function because that would cause you to
receive only one row... containing just the highest view count and no other
data. The querey above will return the entire row for each of the two
records having the two highest values in views. There is no guarantee what
will happen if more than one two rows match the highest value or if two or
more rows match the second highest value. You might want to have additional
fields specified in the Order By clause to control what happens then.
Perhaps a last viewed date so that the most recently viewed of the most
viewed bubbles to the top.

- Virgil

[YoBro]

Hi,

Thanks for great replies.
And it was so simple, I should have thought have that.

Virgil, thanks for the advice, I will have to add a date last viewed field,
because initially all the stock in the database will be 0. So many are the
same.

Cheers,

YoBro

"Virgil Green" <vj*@obsydian.com> wrote in message
news:q5*******************@newssvr22.news.prodigy. com...

"YoBro" <yo***@wazzup.co.nz> wrote in message
news:AO****************@news.xtra.co.nz...

Hi,

I have a database that stores in a field the number of views for a product.

I want to write a query that gets the two highest views, so I can then
output the result.

I have this so far, but not sure how to adapt it to get the two highest
views.

mysql_query("SELECT max(views) FROM stock WHERE status='enabled' AND

photo1

!= ''");

Micheal was very close:
mysql_query("
SELECT *
FROM stock
WHERE status = 'enabled'
AND photo1 IS NOT NULL
ORDER BY views DESC
LIMIT 2
");

You don't want to use the max function because that would cause you to
receive only one row... containing just the highest view count and no

other data. The querey above will return the entire row for each of the two
records having the two highest values in views. There is no guarantee what
will happen if more than one two rows match the highest value or if two or
more rows match the second highest value. You might want to have additional fields specified in the Order By clause to control what happens then.
Perhaps a last viewed date so that the most recently viewed of the most
viewed bubbles to the top.

- Virgil