How can i do sorting in sql?

Navjot Angra

7y1.4kSQL

Suppose i have following coloumn

AppointDateTime

01-15-2019 21:14:12

01-16-2019 12:05:13

01-16-2019 12:55:47

01-16-2019 17:25:00

01-16-2019 18:19:20

01-17-2019 05:18:30

Now I want to soterd data according to date and as well as time

01-16-2019 12:05:13

01-16-2019 12:55:47

01-16-2019 17:25:00

01-16-2019 18:19:20

01-17-2019 05:18:30

01-15-2019 21:14:12

So how can i do this

Following is my query

USE [docprimenew]

/****** Object: StoredProcedure [BMS].[GetAgentDashboardDetails] Script Date: 01-16-2019 15:47:54 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROC [BMS].[GetAgentDashboardDetails]

(

@ProductID Smallint = null,

@Leads [BMS].[LeadTable] READONLY

)

BEGIN

SET NOCOUNT ON

SET DEADLOCK_PRIORITY LOW

Select * from (

SELECT Distinct

LD.LeadID,

LD.Name,

LD.EmailId,

--STUFF(LD.MobileNo, 1, 6,'******') AS MobileNo,

cast (LD.MobileNo as varchar(50)) AS MobileNo,

LD.CustomerID,

LD.LeadSource,(cast(isnull(CCBD.CallBackDate,'') as datetime)+isnull(CCBD.StartTime,'')) as CallBackDate,

LD.CreatedOn,

LD.UpdatedOn as 'StatusDate',

ZC.City,'Consumer' as ProductName,

Ld.ProductID ,

bd.BookingDateTime as OffercreatedOn,

0 as OfferNumber,

SM.StatusName,

Bd.AppointmentDatetime as AppointmentdateTime,

0 as CustIdentityID

-- ,cast([BMS].[CheckTwoDatesDifference](Bd.DocPrimeBookingID) as varchar(100)) as Time

FROM @Leads AS L

INNER JOIN prime.AppointmentDetails BDWITH(NOLOCK) ON L.LeadID=BD.LEADID and bd.IsActive=1

INNER JOIN CRM.Leaddetails LDWITH(NOLOCK) ON L.LeadID=LD.LeadId

left join BMS.CustomerCallBackDetails CCBD WITH(NOLOCK) ON L.LeadID=CCBD.LeadId and CCBD.IsActive=1

INNER JOIN CRM.LeadStatus LSWITH(NOLOCK) ON L.LeadID = LS.LeadID AND LS.IsLastStatus=1

INNER JOIN CRM.Statusmaster SMWITH(NOLOCK) ON SM.StatusID = LS.StatusID and SM.IsActive=1

LEFT JOIN import.zonecities ZCWITH(NOLOCK) ON ZC.CityID=LD.CityID

)

--as a ORDER BY OffercreatedOn DESC

as a ORDER BY AppointmentdateTime

END

8 Replies

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

Manoj Bhoir7y ago
Hi,

You want output which is mentioned in Table2.

Expected Sorted Output : Table 2
01-16-2019 12:05:13
01-16-2019 12:55:47
01-16-2019 17:25:00
01-16-2019 18:19:20
01-17-2019 05:18:30
01-15-2019 21:14:12 <- This should be row 1 if data is in asc order.

But here As you are saying your requirement is data should be sorted first by date and then time. In this case If I am not wrong your date format is MM-dd-yyyy HH:mm:ss
Then in ascending order date order should be 15,16 and 17 not 16,17 and 15.
Or else if you are looking for some other logic please provide some more details.
0
Shraddha Patel7y ago
The ORDER BY statement in sql is used to sort the fetched data in either ascending or descending according to one or more columns.
By default ORDER BY sorts the data in ascending order.
We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.
Syntax of all ways of using ORDER BY is shown below:
Sort according to one column: To sort in ascending or descending order we can use the keywords ASC or DESC respectively.
Syntax:
SELECT * FROM table_name ORDER BY column_name ASC|DESC
table_name: name of the table.
column_name: name of the column according to which the data is needed to be arranged.
ASC: to sort the data in ascending order.
DESC: to sort the data in descending order.
| : use either ASC or DESC to sort in ascending or descending order
Sort according to multiple columns: To sort in ascending or descending order we can use the keywords ASC or DESC respectively. To sort according to multiple columns, separate the names of columns by (,) operator.
Syntax:
SELECT * FROM table_name ORDER BY column1 ASC|DESC , column2 ASC|DESC
0
Munesh Sharma7y ago
https://www.tutorialspoint.com/sql/sql-sorting-results.htm

https://www.geeksforgeeks.org/sql-order-by/
0
Shahriar Nikkhah7y ago
You have to check and see what Index is been used on that field that is sorting the data as you wish then use ORDER BY on that field so that it created what you are looking for in your table 2

OR

If your main table that has the datatime, see if it has a identity field that you can use the ORDER BY on
0
Jignesh Kumar7y ago
Try below,
ORDER BY CONVERT(DATE, AppointmentdateTime),
  CASE WHEN CONVERT(DATE,AppointmentdateTime) = AppointmentdateTime
  THEN 2 ELSE 1 END,
  CONVERT(TIME, AppointmentdateTime);
0
Kapil Gaur7y ago
Try first just sorting the data without any formating and then cast it to format in the way you want.
0
Navjot Angra7y ago
Actually i am not getting data as correct format as i mentioned in second table
So i want to create query so that i acheived that table.

Like :
Table 2
01-16-2019 12:05:13
01-16-2019 12:55:47
01-16-2019 17:25:00
01-16-2019 18:19:20
01-17-2019 05:18:30
01-15-2019 21:14:12

0
Kapil Gaur7y ago
So is this query working or not? Or you need some help? I mean please indicate in your question whether its an information for all readers or it is just a question and you need inputs. Reading above I am little bit confuse.
0