How to Use Order BY Without Group By in Sql Server

Nikunj Satasiya

8y6.1k

Hello Everyone,

Can anyone know how to use order by without use of group by ?

Problem :

I have some of col in my sql query of select like where i also used some aggregate functions now i want to use one col EntryDate from my table but didn't want to use that in group by.

Example:

SELECT ID, CONVERT(VARCHAR(15), EntryDate, 101) AS Entry_Date , NAME, SUM(PRICE) FROm MYTABLE WITH ( NOLOCK ) GROUP BY ID, NAME, CONVERT(VARCHAR(15), EntryDate, 101)

But I Want Something like this without use Group By

SELECT ID, CONVERT(VARCHAR(15), EntryDate, 101) AS Entry_Date , NAME, SUM(PRICE) FROm MYTABLE WITH ( NOLOCK ) GROUP BY ID, NAME, CONVERT(VARCHAR(15), EntryDate, 101)
ORDER BY EntryDate

Can Anyone Help me Please ?

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
Here is what I tried:
declare @myTable table
(
   [Id] integer identity(1, 1) not null primary key clustered
  ,[entrydate] datetime not null
  ,[name] varchar(50) not null
  ,[price] decimal(10, 2) not null
)

insert into @myTable values
('2018-07-26 15:10:33.383', 'Some Name 1', 678.90)
,('2018-07-26 11:10:33.383', 'Some Name 2', 678.90)
,('2018-07-26 09:10:33.383', 'Some Name 3', 678.90)
,('2018-07-25 15:10:33.383', 'Some Name 1', 678.90)
,('2018-07-25 14:10:33.383', 'Some Name 4', 678.90)
,('2018-07-24 18:10:33.383', 'Some Name 1', 678.90)
,('2018-07-23 05:10:33.383', 'Some Name 1', 678.90)

SELECT ID,  CONVERT(VARCHAR(15), EntryDate, 101) AS Entry_Date , NAME, SUM(PRICE), entrydate
FROm @myTable
GROUP BY ID, NAME, CONVERT(VARCHAR(15), EntryDate, 101) , entrydate
ORDER BY entrydate
+1
Nikunj Satasiya8y ago
Thanks Nitin Sir I got your point what you actually trying to say Thanks for your opinion.
0
Nitin Sontakke8y ago
If you ask me, honestly, I am of the opinion that you should avoid formatting the date at the SQL Server level. Some reasons are as follows:

1/ Date formatting is the client-side business, better done by the client itself. No specifically a database job / lookout.

2/ Because formatted dates loose their data type and become string, they cannot participate in any validation / sorting or type-safe code in business layer or on client side. Not a good idea.
3/ Client may have a requirement of formatting the date as per the regional settings of the user. But here the formatting is hard coded in your code. Once again, not a good idea.
+1
Nikunj Satasiya8y ago
Thanks Nitin Sir, For Your Valueable Time Actually i tryed Same just add some additional functions and finally got solution. Thank You.
0
Nikunj Satasiya8y ago
Actually i got a solution. Thanks everyone for your valueable Replay.
0
Nitin Sontakke8y ago
Why not use the original datetime column (entrydate) from the table in select, group by and order by clause.

You can ignore it ultimately at the final point of consumption.

What is harm in that? Please do let me know.

In any case, even formatted date will be ordered correctly. Only time is the problem, but it is not shown anyway, so how does that matter? Please do let me know.
0
Nitin Sontakke8y ago
Is it possible to put together few rows in a script and paste it here, so that I can play with it.

Then show the expected output.

It is better that way so that there is no confusion.

Please DO NOT paste image, paste text (preferably script) so that I can copy / paste and directly use it.
0
Nikunj Satasiya8y ago
As i Show in my Query Everything is Right But Actually i want to set it order by entry date but didn't want to use that col in select and group by only i want to use it in order by.

Look i use CONVERT(VARCHAR(15), EntryDate, 101) AS Entry_Date col in selection and also use same in group by that give date formate like 07/26/2018.

But i want to Arrange all my records order ny same col EntryDate but its actual gormate something like 2018-07-26 15:10:33.383.

So I want to use 2018-07-26 15:10:33.383 formate for set my record order by this entry date.

I hope you can understand what i m trying to say.
0
Nitin Sontakke8y ago
Few things first:

What is the reason / rationale behind you not wanting the GROUP BY clause in your select?

Do you even know what is the purpose of the GROUP BY clause?

What is the problem / issue / undesirable about the second query you have given? Looks perfectly okay and working one to me.
0
Jenith Thakkar8y ago
So you want to do Sum of your price without doing Group By Right?

if yes then you can try partition over by for that Like this

SELECT YEAR(OrderDate), SalesOrderID, CustomerID, TotalDue,
SUM(TotalDue) OVER() AS 'Total Business Sales',
SUM(TotalDue) OVER (PARTITION BY YEAR(OrderDate)) AS 'Total Annual Sales',
SUM(TotalDue) OVER (PARTITION BY CustomerID) AS 'Total Customer Sales'
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, YEAR(OrderDate)

I hope it will helpful for you
0