CODEWITHSUNDEEP
sqlsql-server
Kev
Kev

Reputation: 306

MSSQL Select Distinct and Sorting

I have a query which returns distinct records on 2 columns, however I need to sort the results on those 2 columnns and 1 additional column. When I try the SQL below I get the error shown.

SQL:

SELECT DISTINCT vers, revs FROM tblMVer 
WHERE mid = 194 ORDER BY date_deployed DESC, vers DESC, revs DESC

Error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Any ideas on how to achieve this please.

Thanks

Kev

Upvotes: 2

Views: 5025

Answers (5)

Prinesh Farkya
Prinesh Farkya

Reputation: 1

Execute the following query to get your result 

SELECT DISTINCT tblMVer.vers,
tblMVer.revs 
FROM tblMVer 
WHERE mid = 194 
ORDER BY tblMVer.date_deployed DESC, 
tblMVer.vers DESC, 
tblMVer.revs DESC

Upvotes: 0

Darka
Darka

Reputation: 2768

You cann't order by Date simply because they are different I guess. But if you will take last date, you can do like this:

SELECT vers, revs 
FROM (
    SELECT MAX(date_deployed) AS d, vers, revs 
    FROM tblMVer 
    WHERE mid = 194 
    GROUP BY vers, revs 
    ORDER BY d DESC, vers DESC, revs DESC
) AS temp

Upvotes: 3

Amit
Amit

Reputation: 15387

No need if there all the columns is in same order, if these are in different order then need to specify 

SELECT DISTINCT vers, revs FROM tblMVer 
WHERE mid = 194 ORDER BY  vers , revs DESC

Upvotes: 1

Aditya Jhunjhunwala
Aditya Jhunjhunwala

Reputation: 635

Order By clause can have only those fields which are being selected, so in order to use order by on date_deployed add it to the select statement.

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

There is no date_deployed in select

Try this

SELECT DISTINCT vers, revs
FROM tblMVer 
WHERE mid = 194 
ORDER BY vers,revs DESC

but Still you want to order by date_deployed

Try this

SELECT vers, revs
FROM
(
    SELECT DISTINCT vers, revs,date_deployed 
    FROM tblMVer 
    WHERE mid = 194 
    ORDER BY vers,revs,date_deployed  DESC
) AS S

Upvotes: 1

Related Questions