February 1, 2010 at 11:41 am
I have a table with 1 PK, and this table contains multiple records per employee.
So, I would like to be able to get a dataset for all the employees having to show the latest record entry for each employee.
Example:
1, "john doe", 12345, 01/01/2010
1, "john doe", 12345, 01/15/2010
2, "Jack, OneEye", 3434, 01/01/2010
1, "john does", 12345, 01/20/2010
2, "Jack, OneEye", 3434, 01/10/2010
needed results:
1, "john doe", 12345, 1/20/2010
2, "Jack, OneEye", 3434, 1/10/2010
Hope this makes sense.
I know that I can use the Cursor to get what I want, or even using multiple queries in conjunction to temp tables..
but, I am looking for an atomic function or a sp if not all in TSQL.
In another word, I like this to be done with the minimum number of times taking the water bucket to the well.
thx for any information.
JohnE
Cheers,
John Esraelo
February 1, 2010 at 12:05 pm
Did you try the GROUP BY clause (for details please see BOL)?
February 1, 2010 at 12:20 pm
I have looked at that too, but, I have so many fields in this record structure and also there are couple of memo / text fields. So, I am not sure if the aggregation would work with this.. But the second fresh pair of eyes might help me here.. I will revisit that..
I wonder if self-querying the same table (recursively) would work. hmmm
Cheers,
John Esraelo
February 1, 2010 at 12:58 pm
You could wrap the GROUP BY into a subquery or a CTE and join that to your base table.
I didn't know the number of columns nor do I know if you have any kind of a identifier column that could be used...
February 1, 2010 at 2:57 pm
how about the statement in below.. I did get some assistance on this paragraph. .Now, I have to find out how to use this in a table-value function.
thx for the information guys..
🙂
;with cte as
(
select *, row_number() over (partition by [emp id fk] order by [appdate] desc) as seqnum
from mydb.dbo.applications
)
select *
from cte where seqnum =1 and [emp id fk] = 4
Cheers,
John Esraelo
February 1, 2010 at 3:07 pm
Why would you need a function?
If you want to return all [emp id fk], simply remove "and [emp id fk] = 4" from your statement and you'll get the latest entry for all emp id's.
What would be the purpose of that function and how would you use it? There might be easier ways to do what you want to do...
February 1, 2010 at 3:13 pm
that's a very good point and I am glad that you brought that up.
you see, I have a
> data warehouse
> there is another table joining this table / dataset
therefore, in my database dataset view of my project / solution I would like to convert my table (that is currently in my data view) into a named query that would only bring back the new dataset and eventually I will be writing that to a dim table ...
I hope that makes sense..
now, to answer to your question, yes, you are right I probably do not need the function and I can just grab my statement without the parameter and replace my table in the data warehouse project (data view) and call it the day..
I will let you know..
And, if you have other solutinos please.. by all means.. I am all ears..
thx
Cheers,
John Esraelo
February 1, 2010 at 3:30 pm
FYI
just an update:
I did use the statement, minus the ID filter, in my data warehouse and it failed during the creation of a named query.
What's interesting is that it shows the returned records perfectly but it won't allow me to save it because of the either syntax or the semantics..
keep on trying unless you folks have a better idea(s).
thx a bunch again guys..
Cheers,
John Esraelo
February 1, 2010 at 3:54 pm
If you try to save it as a view then you'd need to remove the semicolon from the beginning of the statement, since it actually is the end of the previous statement (whereas it isn't allowed to have multiple statements when declaring a view).
February 1, 2010 at 3:57 pm
very good thinking, but, that did not work.. same error message about creating this type of query in a "named query" in data view part of the solution.
🙁
Cheers,
John Esraelo
February 2, 2010 at 5:58 am
A couple of ways that work pretty well. First, and this will work in 2000/05/08, is to use a sub-select with the TOP and ORDER BY operation like this:
SELECT ...
FROM TableX x
JOIN TableY y
ON x.id = y.Id
and y.Date = (SELECT TOP 1 y2.Date
FROM TableY y2
WHERE x.ID = y2.ID
ORDER BY y2.Date DESC)
A newer way to do it, although I've found that it doesn't always perform quite as well, is to use the ROWNUMBER function with ORDER BY and PARTITION
WITH y2 AS (
SELECT y.Id
,y.Date
,ROWNUMBER() OVER (PARTITION BY y.ID ORDER BY y.Date DESC) AS RowNum
FROM y)
SELECT ....
FROM TableX x
JOIN y2
ON x.Id = y.ID
and y.RowNum = 1
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply