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