I made below query and I am not getting any data? Any advise please? Thanks.
from employee a
a.empid = a2.empid and
Convert(varchar(10), convert(datetime,A2.FDT)) + CAST(A2.SEQ as varchar) =
(select MAX(Convert(varchar(10), convert(datetime,A3.FDT)) + CAST(A3.seq as varchar))
from employee a3
where a3.EMPID = A2.EMPID AND
convert(varchar(10), Convert(datetime,A3.FDT)) + CAST(A3.SEQ as varchar) <
convert(varchar(10),A.FDT,101)+ CAST(A.SEQ as varchar))
The subquery has a check to insure that the SEQ is less than the SEQ from the outer query, so they can never be equal.
If you're just trying to list the row with max SEQ, do this:
SELECT e.* /*empid, ...*/
SELECT *, ROW_NUMBER() OVER(PARTITION BY empid ORDER BY SEQ DESC) AS row_num
) AS e
WHERE row_num = 1
thanks Scott. Yes I am trying to get the max but Not sure where I can use this? my query was a conversion form Oracle and was trying to equate that to SQL. this was part of a sub query I was trying to convert. see below.
So to_char conversion = convert(varchar(10),FDT,105) looks like this is the conversion to SQL but probably needs more conversion to concat the two: (to_char(A2.FDT,'YYYYMMDD') || to_char(A2.SEQ)… this is where I am not getting data? there should be an easy way to do this, I am still researching.
(to_char(A2.FDT,'YYYYMMDD') || to_char(A2.SEQ) =
--(select max(to_char(a3.fdt,'YYYYMMDD') || to_char(a3.seq))
--from employee a3
--where a3.EMPID = A2.EMPID
-- and to_char(a3.fdt,'YYYYMMDD') || to_char(a3.seq) <
-- to_char(A.FDT,'YYYYMMDD') || to_char(A.SEQ)))
That code is functionally different than what you posted originally.
But there is no need, ever, to concatenate the rows into a single column, that will just slow down processing, possibly slow it down a lot. I know that's common to do in Oracle, but it's still a terrible idea.
Yes I was trying to put it in simpler way to try to run it and see if it works.then I would insert it back to sub query, part of validating data..