sql help + displaying particular rows at the top

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
oliur
Forum Commoner
Posts: 29
Joined: Tue May 26, 2009 3:43 am

sql help + displaying particular rows at the top

Post by oliur »

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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: sql help + displaying particular rows at the top

Post by mikosiko »

user ORDER BY DESC over the field that represent the flag value
oliur
Forum Commoner
Posts: 29
Joined: Tue May 26, 2009 3:43 am

Re: sql help + displaying particular rows at the top

Post by oliur »

thanks a lot :)
oliur
Forum Commoner
Posts: 29
Joined: Tue May 26, 2009 3:43 am

Re: sql help + displaying particular rows at the top

Post by oliur »

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:

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
	";

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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: sql help + displaying particular rows at the top

Post by Eran »

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

Code: Select all

ORDER BY  `featured` DESC, `distance` ASC
The rows will first be sorted by the 'featured' column and after that, each subgroup will be sorted by 'distance'.
oliur
Forum Commoner
Posts: 29
Joined: Tue May 26, 2009 3:43 am

Re: sql help + displaying particular rows at the top

Post by oliur »

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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: sql help + displaying particular rows at the top

Post by Eran »

Hope it removed the confusion. Let me know if you need to know anything else.
You didn't answer my question - please give an example of the expected results, not what you are getting from your query right now.
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.
User avatar
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

Post by John Cartwright »

I think you just want to reverse the ORDER clause columns, i.e.,

Code: Select all

ORDER BY `distance` ASC, `featured` DESC
oliur
Forum Commoner
Posts: 29
Joined: Tue May 26, 2009 3:43 am

Re: sql help + displaying particular rows at the top

Post by oliur »

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)
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: sql help + displaying particular rows at the top

Post by mikosiko »

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
oliur
Forum Commoner
Posts: 29
Joined: Tue May 26, 2009 3:43 am

Re: sql help + displaying particular rows at the top

Post by oliur »

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
But if I write WHERE distance<15 it doesn't work. (EVEN after I declared the distance equation as `distance` )

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?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: sql help + displaying particular rows at the top

Post by mikosiko »

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
that is EXACTLY what I was saying with "distance" is not greater than example.
oliur wrote: But if I write WHERE distance<15 it doesn't work. (EVEN after I declared the distance equation as `distance` )
You need to read a little more about SELECT sentence :wink:
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?
WHAT?.... you already did it... what are you talking about? :?: :?: :?:
oliur
Forum Commoner
Posts: 29
Joined: Tue May 26, 2009 3:43 am

Re: sql help + displaying particular rows at the top

Post by oliur »

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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: sql help + displaying particular rows at the top

Post by mikosiko »

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
Post Reply