locked
Sort by DocDate RRS feed

  • Question

  • User-797751191 posted

    Hi

      I have below query & i want to sort by DocDate

    SELECT
    		CASE WHEN RR = 1 THEN Entry ELSE Entry END 'Internal No',
    		CASE WHEN RR = 1 THEN DocNum  ELSE DocNum END 'AP Inv. #',
    		CASE WHEN RR = 1 THEN DocDate ELSE DocDate END 'Doc. Date'
    	FROM
    		(SELECT
    		(ROW_NUMBER() OVER(PARTITION BY DocEntry ORDER BY DocEntry)) [RR], *
    		FROM #tbl1 )  SUBQUERY1 

    Thanks

    Wednesday, May 20, 2020 11:09 AM

Answers

  • User-719153870 posted

    Hi jsshivalik,

    The basic logic from this query is select Entry,DocNum,DocDate from (another select statement) alias.

    As you can see, this is just a normal select statement like select name from Students, so add your order by clause at the end shall achieve your requirement.

    Please check below demo:

    create table AAA(id int,age int,sdate date,itype varchar(10),anum int,edate date,name varchar(20),num int,snum int)
    insert into AAA values(1,30,'2019-05-03','C',7,'2019-05-03','ABC',200,27400),
    (2,31,'2019-05-02','C',12,'2019-05-02','XYZ',193,28950),(3,32,'2019-05-04','C',14,'2019-05-04','YYY',2000,26620),
    (3,32,'2019-05-04','C',14,'2019-05-04','YYY',4000,10000)
    
    select * from AAA
    SELECT
    	CASE WHEN RR = 1 THEN convert(varchar(10),id) ELSE '' END id,
    	CASE WHEN RR = 1 THEN convert(varchar(10),age) ELSE '' END age,
    	CASE WHEN RR = 1 THEN convert(varchar(10),sdate,103) ELSE '' END sdate,
    	CASE WHEN RR = 1 THEN itype ELSE '' END itype,
    	CASE WHEN RR = 1 THEN convert(varchar(10),anum) ELSE '' END anum,
    	CASE WHEN RR = 1 THEN convert(varchar(10),edate) ELSE '' END edate,
    	CASE WHEN RR = 1 THEN name ELSE '' END name,
    	num,
    	snum
    FROM
    	(SELECT
    	(ROW_NUMBER() OVER(PARTITION BY id ORDER BY id)) [RR], *
    	FROM AAA) SUBQUERY1 order by edate asc
    drop table AAA

    Here's the result of this demo:

    In addition, please always provide complete code in a new thread, this could make it much easier for the community to understand the requriement.

    Thanks for your cooperation!

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 21, 2020 5:10 AM

All replies

  • User452040443 posted

    Hi,

    I don't know if I understand your question correctly but try to change the Order By of the Row_Number function:

    		ROW_NUMBER() OVER(PARTITION BY DocEntry ORDER BY DocDate) [RR]

    Hope this help

    Wednesday, May 20, 2020 12:12 PM
  • User-797751191 posted

    Hi

      I tried this but it didnt't sorted

    Thanks

    Wednesday, May 20, 2020 1:44 PM
  • User452040443 posted

    Can you post an example with an excerpt of the returned result and how would you like it to look?

    Wednesday, May 20, 2020 2:43 PM
  • User-719153870 posted

    Hi jsshivalik,

    The basic logic from this query is select Entry,DocNum,DocDate from (another select statement) alias.

    As you can see, this is just a normal select statement like select name from Students, so add your order by clause at the end shall achieve your requirement.

    Please check below demo:

    create table AAA(id int,age int,sdate date,itype varchar(10),anum int,edate date,name varchar(20),num int,snum int)
    insert into AAA values(1,30,'2019-05-03','C',7,'2019-05-03','ABC',200,27400),
    (2,31,'2019-05-02','C',12,'2019-05-02','XYZ',193,28950),(3,32,'2019-05-04','C',14,'2019-05-04','YYY',2000,26620),
    (3,32,'2019-05-04','C',14,'2019-05-04','YYY',4000,10000)
    
    select * from AAA
    SELECT
    	CASE WHEN RR = 1 THEN convert(varchar(10),id) ELSE '' END id,
    	CASE WHEN RR = 1 THEN convert(varchar(10),age) ELSE '' END age,
    	CASE WHEN RR = 1 THEN convert(varchar(10),sdate,103) ELSE '' END sdate,
    	CASE WHEN RR = 1 THEN itype ELSE '' END itype,
    	CASE WHEN RR = 1 THEN convert(varchar(10),anum) ELSE '' END anum,
    	CASE WHEN RR = 1 THEN convert(varchar(10),edate) ELSE '' END edate,
    	CASE WHEN RR = 1 THEN name ELSE '' END name,
    	num,
    	snum
    FROM
    	(SELECT
    	(ROW_NUMBER() OVER(PARTITION BY id ORDER BY id)) [RR], *
    	FROM AAA) SUBQUERY1 order by edate asc
    drop table AAA

    Here's the result of this demo:

    In addition, please always provide complete code in a new thread, this could make it much easier for the community to understand the requriement.

    Thanks for your cooperation!

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 21, 2020 5:10 AM