I have Table in which We can have Same ticket no.for multiple entries.i want to retrieve most recent ticket(of same ticket no.) number based on Initiated on column.
我有一个表,其中我们可以为多个条目提供相同的票号。我想根据列上的“已启动”检索最近的票证(相同票号)。
Ticket VendorTicket InitiatedOn Comments
198165 test 2012-08-10 16:31:33.707 test
199485 sagar 2012-08-11 12:36:25.720 sagar
199485 sagar1 2012-08-11 14:36:25.720 sagar1
199478 kishor 2012-08-11 12:37:21.923 kishor
I have Written this query in T-SQL in Stored Procedure It gives me Error Saying xObj = {"Incorrect syntax near '.'."}
我已经在存储过程中的T-SQL中编写了这个查询它给了我错误说xObj = {“''附近的语法不正确。”。}
My T-SQL
`@SQL='Select ''<a href="javascript:editTicketByIDAction('' + CONVERT(VarChar(Max), Ticket) + '')">'' + CONVERT(VarChar(Max),Ticket) + ''</a>'' t1.Ticket,t1.VendorTicket[Vendor Ticket],t1.Comments
From VendorTickets t1
WHERE NotifyOn <= GetDate() And NotifyOn Is Not Null AND
NOT EXISTS
(SELECT * FROM VendorTickets t2
WHERE t1.Ticket = t2.Ticket AND t1.InitiatedOn < t2.InitiatedOn)'`
6 个解决方案
#1
2
A general solution (works on any database) to this problem is the following:
此问题的一般解决方案(适用于任何数据库)如下:
SELECT * FROM [my_table] t1
WHERE NOT EXISTS (
SELECT * FROM [my_table] t2
WHERE t1.Ticket = t2.Ticket
AND t1.InitiatedOn < t2.InitiatedOn
)
This reads: Get all value from my_table where there is no ticket with the same Ticket
ID and a more recent date.
其内容如下:从my_table获取所有值,其中没有具有相同故障单ID和更新日期的故障单。
Notes:
- This may return duplicates in case there are two "most recent" tickets with identical
InitiatedOn
value. - Be sure to have appropriate indexes on both
Ticket
andInitiatedOn
columns
如果有两个具有相同InitiatedOn值的“最新”票证,则可能会返回重复项。
确保Ticket和InitiatedOn列上都有适当的索引
#2
1
try this:
This will give most recent records from each group
这将给出每组的最新记录
select * from <table> t join
(select Ticket,max(InitiatedOn) as InitiatedOn
from <table>
group by Ticket)a
on t.Ticket=a.Ticket
and t.InitiatedOn=a.InitiatedOn
#3
1
SELECT Ticket,
VendorTicket,
InitiatedOn,
Comments
FROM (
SELECT Ticket,
VendorTicket,
InitiatedOn,
Comments,
ROW_NUMBER() OVER (PARTITION BY Ticket ORDER BY InitiatedOn DESC) AS Row
FROM Table
) AS Q
WHERE Q.Row = 1
#4
0
If row_number() over()
is available for you.
如果row_number()over()可用。
select T.Ticket,
T.VendorTicket,
T.InitiatedOn,
T.Comments
from (
select Ticket,
VendorTicket,
InitiatedOn,
Comments,
row_number() over(partition by Ticket order by InitiatedOn desc) as rn
from YourTable
) T
where T.rn = 1
#5
0
Select t1.*
from tickets t1
left join tickets t2 on (t1.ticket = t2.ticket and t2.initiatedon > t1.initiatedon)
where t2.ticket is null;
#6
-1
select * from table where ticket=199485 order by InitiatedOn desc limit 1;
#1
2
A general solution (works on any database) to this problem is the following:
此问题的一般解决方案(适用于任何数据库)如下:
SELECT * FROM [my_table] t1
WHERE NOT EXISTS (
SELECT * FROM [my_table] t2
WHERE t1.Ticket = t2.Ticket
AND t1.InitiatedOn < t2.InitiatedOn
)
This reads: Get all value from my_table where there is no ticket with the same Ticket
ID and a more recent date.
其内容如下:从my_table获取所有值,其中没有具有相同故障单ID和更新日期的故障单。
Notes:
- This may return duplicates in case there are two "most recent" tickets with identical
InitiatedOn
value. - Be sure to have appropriate indexes on both
Ticket
andInitiatedOn
columns
如果有两个具有相同InitiatedOn值的“最新”票证,则可能会返回重复项。
确保Ticket和InitiatedOn列上都有适当的索引
#2
1
try this:
This will give most recent records from each group
这将给出每组的最新记录
select * from <table> t join
(select Ticket,max(InitiatedOn) as InitiatedOn
from <table>
group by Ticket)a
on t.Ticket=a.Ticket
and t.InitiatedOn=a.InitiatedOn
#3
1
SELECT Ticket,
VendorTicket,
InitiatedOn,
Comments
FROM (
SELECT Ticket,
VendorTicket,
InitiatedOn,
Comments,
ROW_NUMBER() OVER (PARTITION BY Ticket ORDER BY InitiatedOn DESC) AS Row
FROM Table
) AS Q
WHERE Q.Row = 1
#4
0
If row_number() over()
is available for you.
如果row_number()over()可用。
select T.Ticket,
T.VendorTicket,
T.InitiatedOn,
T.Comments
from (
select Ticket,
VendorTicket,
InitiatedOn,
Comments,
row_number() over(partition by Ticket order by InitiatedOn desc) as rn
from YourTable
) T
where T.rn = 1
#5
0
Select t1.*
from tickets t1
left join tickets t2 on (t1.ticket = t2.ticket and t2.initiatedon > t1.initiatedon)
where t2.ticket is null;
#6
-1
select * from table where ticket=199485 order by InitiatedOn desc limit 1;