CODEWITHSUNDEEP
sql-servert-sqlsql-order-by
Shawn
Shawn

Reputation: 2435

Order by Column Outside of Group By

I am trying to order by a column that I do not want to use in my Group By clause.

So the idea here is to be able to order by this FileId.

SELECT FileGuid, FileName, ROW_NUMBER() OVER(Order by FileId) AS rownum
FROM dbo.FileImport
GROUP BY FileGuid, FileName
ORDER BY rownum

Right now I am getting this error:

Column 'dbo.FileImport.FileId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

But if I include that in the group by, I will be getting the record of each file in that associated to that FileGuid, which I don't want to happen. Thanks in advance on any input on how I can get this working.

Upvotes: 1

Views: 228

Answers (3)

nee21
nee21

Reputation: 199

You can try this :

SELECT * FROM 
(SELECT FileGuid, FileName, ROW_NUMBER() OVER(Partition by FileGuid, FileName
Order by FileId) AS rownum
FROM dbo.FileImport) X
WHERE rownum = <<your condition>>
ORDER BY X.FileGuid,X.FileName,X.rownum

With this any type of filter can be applied on rownum by adding outer where clause.

Upvotes: 0

Zulfiqar Ali
Zulfiqar Ali

Reputation: 123

You can use this SQL statement.

Select *
From 
(
  SELECT T.FileGuid, T.FileName, ROW_NUMBER() OVER(Order by T.FileId) AS rownum 
  from (
     SELECT FileGuid, FileName, Max(FileID) As FileID
     FROM dbo.FileImport
     GROUP BY FileGuid, FileName
  )T
)Temp
order by Temp.rownum

Let me know if question!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270483

You have to decide which fileid you care about. Then you can use an aggregation function:

SELECT FileGuid, FileName, ROW_NUMBER() OVER (Order by MIN(FileId)) AS rownum
FROM dbo.FileImport
GROUP BY FileGuid, FileName
ORDER BY rownum;

Upvotes: 3

Related Questions