Fetch data from database in a specific order

Question

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

Shafeeque · Accepted Answer · 2015-06-11 04:48:35Z

1

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

answered Jun 11, 2015 at 4:48

Shafeeque

2,0692 gold badges13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Syed Mansoor · Accepted Answer · 2015-06-11 04:49:34Z

1

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;

answered Jun 11, 2015 at 4:49

Syed Mansoor

111 bronze badge

Comments

Mukesh Kalgude · Accepted Answer · 2015-06-11 05:00:47Z

1

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

answered Jun 11, 2015 at 5:00

Mukesh Kalgude

4,8542 gold badges21 silver badges32 bronze badges

Collectives™ on Stack Overflow

Fetch data from database in a specific order

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related