How to Generated in this Sql Query [Resolved]

Posted by Jayakumars under Sql Server on 1/30/2017 | Points: 10 | Views : 1965 | Status : [Member] [MVP] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi

--- tHIS IS MY DATA

TEMPID TEMPGRPID TEMPDATE
1001 101 2016-10-31
1002 101 2016-11-01
1003 101 2016-11-03
1010 102 2016-10-31
1011 102 2016-11-01
2010 103 2016-10-31
2011 104 2016-11-01

--How to sno order based on tempgrpid
--i NEED LIKE THIS output

SNO TEMPID TEMPGRPID TEMPDATE
1 1001 101 2016-10-31
2 1002 101 2016-11-01
3 1003 101 2016-11-03
1 1010 102 2016-10-31
2 1011 102 2016-11-01
1 2010 103 2016-10-31
1 2011 104 2016-11-01
2 2012 104 2016-11-01

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: A2H on: 1/30/2017 [Member] [MVP] Silver | Points: 50

1	You can use ROW_NUMBER() function like below. Ensure that you change the table names as per your design -- Change the table name as per your design SELECT ROW_NUMBER() OVER (PARTITION BY TEMPGRPID, TEMPGRPID ORDER BY TEMPGRPID, TEMPGRPID) AS SNO, tbl.TEMPID, tbl.TEMPGRPID, tbl.[Date] FROM SampleTable AS tbl ORDER BY tbl.TEMPID Thanks, A2H My Blog Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

You can use ROW_NUMBER() function like below. Ensure that you change the table names as per your design

-- Change the table name as per your design
SELECT
    ROW_NUMBER() OVER (PARTITION BY TEMPGRPID, TEMPGRPID ORDER BY TEMPGRPID, TEMPGRPID) AS SNO,
    tbl.TEMPID, 
    tbl.TEMPGRPID,
    tbl.[Date]
FROM   SampleTable AS tbl
ORDER  BY tbl.TEMPID

Thanks,
A2H
My Blog

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts