CODEWITHSUNDEEP
sqlsql-server
Mahyar
Mahyar

Reputation: 796

SQL where in sql subquery with order by

This is my query

SELECT * FROM Place WHERE Place.Id IN (
    SELECT TOP 10 PlaceId from #ResultPlaceList order by CPlaceId desc)

my result is ordered by Place.ID, but i want to have a result with ordered CPlaceId.

Upvotes: 0

Views: 66

Answers (4)

Neha
Neha

Reputation: 197

'SELECT C.ID FROM CustomerDetails C LEFT JOIN UserInfo U ON C.ID = U.ID Order by U.ID'

Can you modify your query like this

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39517

You could use a join to use the cPlaceId column for ordering:

select p.*
from Place p
join (
    select top 10 PlaceId,
        CPlaceId
    from #ResultPlaceList
    order by CPlaceId desc
    ) r on p.Id = r.PlaceId
order by r.CPlaceId;

Upvotes: 2

imtheman
imtheman

Reputation: 4843

You will have to do a JOIN to accomplish this. It will look something like this:

SELECT TOP 10 Place.* 
FROM Place
JOIN #ResultPlaceList ON #ResultPlaceList.PlaceId = Place.Id
ORDER BY CPlaceId DESC

Upvotes: 1

Pons
Pons

Reputation: 1111

Something like this....

SELECT * FROM PLACE WHERE PLACE.ID IN (
    SELECT PLACEID
    FROM (
        SELECT PLACEID
            , ROW_NUMBER() OVER (ORDER BY CPLACEID DESC) RNUM
        FROM #RESULTPLACELIST
    ) TMP
    WHERE RNUM <= 10
)

Upvotes: 1

Related Questions