sql help + displaying particular rows at the top
Moderator: General Moderators
sql help + displaying particular rows at the top
If I have a table with some featured stores (flagged 1) and normal stores(flagged 0), how can I display the featured stores first followed by the normal stores. Could that be done in one sql command?
Thanks
Thanks
Re: sql help + displaying particular rows at the top
user ORDER BY DESC over the field that represent the flag value
Re: sql help + displaying particular rows at the top
thanks a lot 
Re: sql help + displaying particular rows at the top
okay I am stuck again,
please could anyone advice
1) how to order by featured stores and distance together?
for example I want something like this:
Featured Stores:
Kevin's store (0.4 miles)
Alex's store (0.7 miles)
Normal Stores:
Cynthia's Store(0.1 mile) [notice this is even the closest one but not featured store]
Alma's store (4.5 miles)
Is it possible to run one single sql query and display the result as above?
Or, do i need to find the featured list first (i.e ORDER BY `distance` ASC, `featured` DESC, `store_name` ASC)
and another query for the normal store (i.e ORDER BY `distance` ASC, `store_name` ASC) ?
I've tried this:
But it will keep the featured stores at the very top in any search result no matter how far they are from user's location.
If it is possible to achieve this with one query, can you show me an example please?
Thanks very much for your help and time.
please could anyone advice
1) how to order by featured stores and distance together?
for example I want something like this:
Featured Stores:
Kevin's store (0.4 miles)
Alex's store (0.7 miles)
Normal Stores:
Cynthia's Store(0.1 mile) [notice this is even the closest one but not featured store]
Alma's store (4.5 miles)
Is it possible to run one single sql query and display the result as above?
Or, do i need to find the featured list first (i.e ORDER BY `distance` ASC, `featured` DESC, `store_name` ASC)
and another query for the normal store (i.e ORDER BY `distance` ASC, `store_name` ASC) ?
I've tried this:
Code: Select all
"SELECT *,(((acos(sin((".$latitude."*pi()/180)) * sin((`lat`*pi()/180))
+cos((".$latitude."*pi()/180)) * cos((`lat`*pi()/180))
* cos(((".$longitude."- `lng`)*pi()/180))))*180/pi())*60*1.1515)
as distance
FROM `stores`
ORDER BY `featured` DESC, `distance` ASC
LIMIT 0,10
";
If it is possible to achieve this with one query, can you show me an example please?
Thanks very much for your help and time.
Re: sql help + displaying particular rows at the top
You didn't specify exactly how your results differ from what you want to achieve.
In the example you gave, the featured stores are at the top, as you say happens in your query results. How do you want to change it?
When you write
The rows will first be sorted by the 'featured' column and after that, each subgroup will be sorted by 'distance'.
In the example you gave, the featured stores are at the top, as you say happens in your query results. How do you want to change it?
When you write
Code: Select all
ORDER BY `featured` DESC, `distance` ASCRe: sql help + displaying particular rows at the top
I am sorry if I wasn't clear enough.
Okay here are the steps:
1. User types - an area i.e London or a postcode NW11
2. They see the 10 closests featured stores as well as normal stores.
My query doesn't serve the purpose. Because, it ignores the distance and displays any featured stores first and then the normal stores
So, if someone types Scotland (provided there are no featured stores in Scotland)
it will pick featured stores from London and display as follows:
1. Featured store 1 (300 miles)
2. Featured store 2 (310 miles)
3. Normal store (0.4 miles)
4. Normal store (2 miles)
I suppose it's not working because the query is sorting the featured stores even before it sorts results by distance. And if I sort by distance first the normal store will appear before the featured stores (which I dont want)
Hope it removed the confusion. Let me know if you need to know anything else.
Okay here are the steps:
1. User types - an area i.e London or a postcode NW11
2. They see the 10 closests featured stores as well as normal stores.
My query doesn't serve the purpose. Because, it ignores the distance and displays any featured stores first and then the normal stores
So, if someone types Scotland (provided there are no featured stores in Scotland)
it will pick featured stores from London and display as follows:
1. Featured store 1 (300 miles)
2. Featured store 2 (310 miles)
3. Normal store (0.4 miles)
4. Normal store (2 miles)
I suppose it's not working because the query is sorting the featured stores even before it sorts results by distance. And if I sort by distance first the normal store will appear before the featured stores (which I dont want)
Hope it removed the confusion. Let me know if you need to know anything else.
Re: sql help + displaying particular rows at the top
You didn't answer my question - please give an example of the expected results, not what you are getting from your query right now.Hope it removed the confusion. Let me know if you need to know anything else.
What I'm trying to figure out is how you want the featured stores to mix with normal stores in the results, considering the distance as well.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: sql help + displaying particular rows at the top
I think you just want to reverse the ORDER clause columns, i.e.,
Code: Select all
ORDER BY `distance` ASC, `featured` DESCRe: sql help + displaying particular rows at the top
Sorry again,
Okay this is how I want my listingS to appear:
FEATURED STORES NEAR YOUR AREA:
ABC STORE (0.4 MILES)
Cyz STORE (2.3 MILES)
OTHER STORES NEAR YOUR AREA:
JAME'S STORE (0.1 MILE)
M STORE (4.1 MILES)
Okay this is how I want my listingS to appear:
FEATURED STORES NEAR YOUR AREA:
ABC STORE (0.4 MILES)
Cyz STORE (2.3 MILES)
OTHER STORES NEAR YOUR AREA:
JAME'S STORE (0.1 MILE)
M STORE (4.1 MILES)
Re: sql help + displaying particular rows at the top
the ORDER BY that you are using is correct (featured DESC, distance ASC) your problem is that you want to display ONLY the 10 locations in the SAME AREA that the user selected (according to your last post) and you are not including that in your select. either that or you have to choose the stores based on a know distance criteria.., p.e when "distance" is not greater that 5 miles
Re: sql help + displaying particular rows at the top
I've managed to get the result using this query,
Apart from this little issue, the query returns what I was trying to achieve.
I guess, to separate the featured stores from the normal stores and arrange their order accordingly in the result view is down to a server side language (php, asp) and nothing to do with SQL, correct?
But if I write WHERE distance<15 it doesn't work. (EVEN after I declared the distance equation as `distance` )SELECT `store_name`,(((acos(sin((51.7290877*pi()/180)) * sin((`lat`*pi()/180)) +cos((51.7290877*pi()/180)) * cos((`lat`*pi()/180)) * cos(((-0.6485139- `lng`)*pi()/180))))*180/pi())*60*1.1515) as distance FROM `stores` WHERE (((acos(sin((51.7290877*pi()/180)) * sin((`lat`*pi()/180)) +cos((51.7290877*pi()/180)) * cos((`lat`*pi()/180)) * cos(((-0.6485139- `lng`)*pi()/180))))*180/pi())*60*1.1515) <15 ORDER BY featured DESC, distance ASC LIMIT 0,10
Apart from this little issue, the query returns what I was trying to achieve.
I guess, to separate the featured stores from the normal stores and arrange their order accordingly in the result view is down to a server side language (php, asp) and nothing to do with SQL, correct?
Re: sql help + displaying particular rows at the top
that is EXACTLY what I was saying with "distance" is not greater than example.oliur wrote:I've managed to get the result using this query,
SELECT `store_name`,(((acos(sin((51.7290877*pi()/180)) * sin((`lat`*pi()/180)) +cos((51.7290877*pi()/180)) * cos((`lat`*pi()/180)) * cos(((-0.6485139- `lng`)*pi()/180))))*180/pi())*60*1.1515) as distance FROM `stores` WHERE (((acos(sin((51.7290877*pi()/180)) * sin((`lat`*pi()/180)) +cos((51.7290877*pi()/180)) * cos((`lat`*pi()/180)) * cos(((-0.6485139- `lng`)*pi()/180))))*180/pi())*60*1.1515) <15 ORDER BY featured DESC, distance ASC LIMIT 0,10
You need to read a little more about SELECT sentenceoliur wrote: But if I write WHERE distance<15 it doesn't work. (EVEN after I declared the distance equation as `distance` )
WHAT?.... you already did it... what are you talking about?oliur wrote: Apart from this little issue, the query returns what I was trying to achieve.
I guess, to separate the featured stores from the normal stores and arrange their order accordingly in the result view is down to a server side language (php, asp) and nothing to do with SQL, correct?
Re: sql help + displaying particular rows at the top
Thanks mikosiko, yes you pointed that distance thing out and I tried and it worked. Thanks a lot mate.
Okay I'll try and figure out the select part
last thing what i basically said is:
the sql command returns all the rows (featured stores at the top followed by all the normal stores. )
But if you would like to have a heading for each section i.e
FEATURED STORES NEAR YOU:
...and then display all the featured stores
NORMAL STORES NEAR YOU:
....and display all the normal stores.
I was talking about this arragements, i.e having heading and separating the featured ones from the normal ones etc.
Okay I'll try and figure out the select part
last thing what i basically said is:
the sql command returns all the rows (featured stores at the top followed by all the normal stores. )
But if you would like to have a heading for each section i.e
FEATURED STORES NEAR YOU:
...and then display all the featured stores
NORMAL STORES NEAR YOU:
....and display all the normal stores.
I was talking about this arragements, i.e having heading and separating the featured ones from the normal ones etc.
Re: sql help + displaying particular rows at the top
Oh.. that!!.... of course... to add some headers before each group you need to store the results of your select and process it accordingly incorporating your headers.
the process more or least is (very simplified):
- Connect to you database (mysql_conect)
- Prepare your query ($query = YOUR SELECT)
- Execute the query ( $resultset = mysql_query($query) )
- Loop over your $resultset (while ($row = mysql_fetch_assoc($resultset)) )
Do what you want with your data
several options/instructions to do the same in php
the process more or least is (very simplified):
- Connect to you database (mysql_conect)
- Prepare your query ($query = YOUR SELECT)
- Execute the query ( $resultset = mysql_query($query) )
- Loop over your $resultset (while ($row = mysql_fetch_assoc($resultset)) )
Do what you want with your data
several options/instructions to do the same in php