CODEWITHSUNDEEP
mysqlsql
user4622110
user4622110

Reputation:

Fetch data from database in a specific order

I have a table named vendor here is a view of it

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 wish to create a list of vendors in the following order (no repetition of vendors) First those vendors should come whose value of staffpick is yes and these staffpick vendors should be arranged in dec order of totalfav value. Then those vendors should come who have totalfav but staffpick value is not yes. The last part comes where those vendors will be displayed who are neither staffpick nor totalfav and these vendors should be arranged in alphabatic order

The list should be like this

V3
V2
V1
V5
V4
A
D

if I try to create an array for every part like this I get a repetition

SELECT * FROM  vendors where staffpick='yes' and favvendor!=''

can anyone tell how I can do the sorting

Upvotes: 1

Views: 553

Answers (3)

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Try to 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

Upvotes: 1

Syed Mansoor
Syed Mansoor

Reputation: 11

You need to add the order by clause in the query. The query will be like this

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

Upvotes: 1

Shafeeque
Shafeeque

Reputation: 2069

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

Upvotes: 1

Related Questions