SQL Order By 1

[Naoise]

tblFoo
ID | Foo
1 apple
2 orange
3 banana
4 pineapple
5 grapefruit

I want a statement to return the following, all the values alphabetically but 'pineapple' always the first result. So

pineapple
apple
banana
grapefruit
orange

 

[r937]

select Foo
  from tblFoo
order
    by case when Foo='pineapple'
            then 0 else 937 end
     , Foo

r937.com | rudy.ca

 

[Naoise]

That's deadly, 937 - the magic number (care to explain it? - this works for a query without distinct, I have

select distinct(Foo).... as there are many entries with similar values for Foo, so when I run the query with DISTINCT I get ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Any ideas? Thanks

 

[r937]

sorry, i don't understand your followup question, but i did notice that you wrote DISTINCT(Foo) and that's wrong, DISTINCT is not a function

r937.com | rudy.ca

 

[Naoise]

Sorry, I was abbreviating

SELECT DISTINCT(Foo) .... should have been

SELECT DISTINCT(Foo) FROM tblFoo
order
by case when Foo='pineapple'
then 0 else 937 end
, Foo

So when I run this query with DISTINCT I get ORDER BY items must appear in the select list if SELECT DISTINCT is specified

 

[r937]

why are you using the parentheses?

DISTINCT is not a function

besides, i don't think you need DISTINCT here at all

r937.com | rudy.ca

 

[Naoise]

Ok, even with parenthese dropped the error is the same. I am simplifying a more complex query to ease the resolution.

tblFoo
ID | Foo
1 apple
2 orange
3 banana
4 pineapple
5 grapefruit
6 apple
7 banana

SELECT DISTINCT Foo, ID FROM tblFoo
order
by case when Foo='pineapple'
then 0 else 937 end
, Foo

I do need the distinct here, trust me, I have ammended the table above to show that there are multiples of each value for Foo. I just need a query that can give me

pineapple
apple
banana
grapefruit
orange

The order by clause worked a treat when used without DISTINCT, but I have to have it in. Any replies appreciated.

 

[r937]

the problem is, if you want to include ID in the SELECT, you will have multiple different (i.e. DISTINCT) rows for each value of Foo

therefore, use GROUP BY instead of DISTINCT, and select only one of the ID values for each value of Foo, arbitrarily chosen here as the lowest ID --

select Foo, min(ID) as min_ID 
  from tblFoo
group
    by Foo
order
    by case when Foo='pineapple'
            then 0 else 937 end
     , Foo

of course, since you said "I just need a query that can give me..." and then listed the results without an ID, then what you really need is this --

select Foo
  from tblFoo
group
    by Foo
order
    by case when Foo='pineapple'
            then 0 else 937 end
     , Foo

r937.com | rudy.ca