CODEWITHSUNDEEP
sqlsql-server-ce
Pontus Svedberg
Pontus Svedberg

Reputation: 305

SQL CE: Select distinct, order by another column

I'm trying to figure out how to order my data that I get, I want to do a select distinct(name) and order by ID like this:

var querythis = "SELECT distinct(exerVariName) as variants FROM Test WHERE date = @0 ORDER BY ID";

So I somehow have to select distinct(id) as well, is this possible to achieve?

Upvotes: 0

Views: 820

Answers (2)

dnoeth
dnoeth

Reputation: 60462

You can't order by a column which is not in your select list when using DISTINCT.

But you get also get a distinct result using GROUP BY:

SELECT exerVariName as variants 
FROM Test 
WHERE date = @0 
GROUP BY exerVariName
ORDER BY MIN(ID) -- or MAX(ID)

Edit:

If CE doesn't allow aggregate functions in ORDER BY you might try a Derived Table (hopefully it's allowed):

SELECT variants 
FROM 
 ( 
    SELECT exerVariName as variants, MIN(ID) as minID -- or MAX(ID)
    FROM Test 
    WHERE date = @0 
    GROUP BY exerVariName
 ) AS dt
ORDER BY minID

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Try this:

select exerVariName as variants,
    MIN(ID) as min_id
from Test
where date = @0
group by exerVariName
order by min_id

Upvotes: 1

Related Questions