[RESOLVED] ORDER BY issue

Dillenger

Hello I am trying to sort my Price column from lowest to highest I use this call

SELECT * FROM menuitems ORDER BY menuPrice1 ASC

The problem is I have a bunch of items with no price (0.00) that are showing up first. is there a way to sort ASC but then put all results with NULL or 0.00 at the end of the results?

Something like this:

SELECT * FROM menuitems ORDER BY menuPrice1  WHERE menuPrice != 0.00 ASC

HalfaBee

I think I would do it with 2 queries, 1 > 0.00 and then the = 0.00 values.

Piranha

You should use case to order this way:

select * from menuitems 
order by 
case p
when menuitem.price == 0.00 then 2
when menuitem.price is null then 3
else 1
end case,
price asc

I hope that code above is correct, haven't had time to check it out. Anyway you get the idea how to handle cases.

Dillenger

I put this in the query but it's returning an error, any suggestions?

select * from menuitems 
order by 
case
when menuPrice1 = '0.00' then 2
when menuPrice1 is null then 3
else 1
end case,
menuPrice1 asc

Piranha wrote:
You should use case to order this way:
select * from menuitems 
order by 
case p
when menuitem.price == 0.00 then 2
when menuitem.price is null then 3
else 1
end case,
price asc
I hope that code above is correct, haven't had time to check it out. Anyway you get the idea how to handle cases.

Dillenger

ok so I kinda figured it out but I get strange results, I am using:

select menuPrice1 from menuitems 
order by 
case menuPrice1
when menuPrice1 is not null then 1
else 2
end
asc

And this is the order I am getting

1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	1.00
	Edit 	Delete 	10.00
	Edit 	Delete 	12.00
	Edit 	Delete 	10.00
	Edit 	Delete 	0.00
	Edit 	Delete 	0.00
	Edit 	Delete 	0.00
	Edit 	Delete 	1.60
	Edit 	Delete 	0.00
	Edit 	Delete 	0.00
	Edit 	Delete 	0.00
	Edit 	Delete 	0.00
	Edit 	Delete 	0.00
	Edit 	Delete 	0.00
	Edit 	Delete 	0.00
	Edit 	Delete 	0.00
	Edit 	Delete 	0.00
	Edit 	Delete 	0.00
	Edit 	Delete 	0.00
	Edit 	Delete 	0.00
	Edit 	Delete 	7.25
	Edit 	Delete 	7.25
	Edit 	Delete 	7.25
	Edit 	Delete 	5.25
	Edit 	Delete 	5.50
	Edit 	Delete 	7.25
	Edit 	Delete 	11.00
	Edit 	Delete 	7.25
	Edit 	Delete 	9.95
	Edit 	Delete 	10.95
	Edit 	Delete 	11.25
	Edit 	Delete 	11.50
	Edit 	Delete 	11.25
	Edit 	Delete 	4.95
	Edit 	Delete 	2.95
	Edit 	Delete 	4.95
	Edit 	Delete 	7.85
	Edit 	Delete 	3.95
	Edit 	Delete 	4.95
	Edit 	Delete 	4.95
	Edit 	Delete 	5.25
	Edit 	Delete 	5.95
	Edit 	Delete 	5.95
	Edit 	Delete 	4.95
	Edit 	Delete 	7.95
	Edit 	Delete 	7.95
	Edit 	Delete 	7.95
	Edit 	Delete 	7.95
	Edit 	Delete 	7.95
	Edit 	Delete 	7.95
	Edit 	Delete 	7.95
	Edit 	Delete 	19.00
	Edit 	Delete 	28.00
	Edit 	Delete 	38.00
	Edit 	Delete 	10.50
	Edit 	Delete 	22.95
	Edit 	Delete 	32.95
	Edit 	Delete 	42.95
	Edit 	Delete 	11.95
	Edit 	Delete 	0.00
	Edit 	Delete 	7.95
	Edit 	Delete 	6.95
	Edit 	Delete 	6.95
	Edit 	Delete 	6.95
	Edit 	Delete 	5.95
	Edit 	Delete 	5.95

Dillenger

Ok, now I'm even closer, but still not there. I now have it so all the 0 prices are last, but all the regular prices are not sorted.

select menuPrice1 from menuitems
order by
case menuPrice1
when 0 then 2
else 1
end
asc

Dillenger

GOT IT!

Thank you for pointing me in the right direction 🙂

select menuPrice1,menuID,menuName from menuitems
order by
case menuPrice1
when 0 then 2
else 1
end, menuPrice1
asc