Clever Geek Handbook

Fetching data from a database in a specific order

I have a table named vendor, here is its view

id  vendorname   staffpick   totalfav
1      V1           yes        1
2      V2           yes        3
3      V3           yes        4
4      V4                      2
5      V5                      5
6      D
7      A

I want to create a list of vendors in the following order (without repeating vendors) These vendors should appear first, whose staffpick cost is yes, and these staff sellers should be placed in descending order of total cost. Then those providers should come that have totalfav, but the staffpick value is not yes. The last part appears where these suppliers will be shown, which are neither full-time nor total, and these suppliers must be listed in alphabetical order.

The list should look like this:

V3
V2
V1
V5
V4
A
D

if i try to create an array for each part like this i get repetition

$sql = "SELECT * FROM  vendors where staffpick='yes' and favvendor!=''";
$result = mysql_query($sql);
$row = mysql_fetch_array($result)

can anyone tell how can i sort

+3


source to share


3 answers


Try something like this



SELECT * 
FROM  vendors
ORDER BY staffpick ='YES' AND totalfav DESC,
         staffpick IS NULL AND totalfav DESC,
         staffpick IS NULL AND totalfav IS NULL AND vendorname ASC
+1


source


You need to add an order by clause to your request. The request will be like this:



SELECT * FROM  vendors where staffpick='yes' and favvendor!='' order by totalfav desc;
+1


source


Try sqlserver

SELECT * 
FROM  vendors
ORDER BY case when staffpick ='YES' then totalfav end DESC,
         case when staffpick IS NULL then totalfav end DESC,
         case when staffpick IS NULL AND totalfav IS NULL then vendorname end ASC
+1


source






More articles:


All Articles