October 6, 2008 at 9:54 am
Hey oh great gurus of SQL-fu
I have a table (heh its SQL go fig). And what I need to do is return the entire table based on 2 things.
1) the "User_ID" must be distinct
2) it has to return the record with the latest Date
My current statement that isn't working is this:
Select DISTINCT User_ID as User_ID_Dup, * from Users ORDER BY Start_Date DESC
I know this doesn't work but Im a bit (ok really rusty) on the SQL class I took 8 years ago. HELP! The "As User_ID_dup" is used is because of some VB code that is utilizing this statement and a serious argument with datatables that have duplicate col names.
October 6, 2008 at 10:15 am
This code will get you the max start date for each unique User_Id
Select User_Id, Max(Start_date) as MaxDate
From Users
Group by User_Id
Order by MaxDate desc
October 6, 2008 at 10:38 am
ok I think I need to clarify a little bit.
Table Users has we will say 5 fields
User_ID, FirstName,LastName,StartDate,AccessLevel
Rows look something like this
User_ID | FirstName | LastName | StartDate | AccessLevel
Jdoe | John | Doe | 1/1/1900 | 1
Jdoe | John | Doe | 10/9/2007 | 3
BSmith | Bob | Smith | 1/1/2000 | 2
BSmith | Bob | Smith | 9/1/2006 | 4
SJones | Sally | Jones | 10/10/1950| 5
SJones | Sally | Jones | 10/10/2004| 2
Now based on the User_ID field and the start date I need a statement that will produce these rows.
Jdoe | John | Doe | 10/9/2007 | 3
BSmith | Bob | Smith | 9/1/2006 | 4
SJones | Sally | Jones | 10/10/2004| 2
And I would really like to avoid typing out every col name if at all possible. Because in reality this table has about 57 cols.
October 6, 2008 at 11:33 am
You need to create a user defined function which will return the lastest date and Access Level.
Someting like
Select distinct User_Id, UserName, dbo.getmaxDate(User_Id), dbo.GetMaxAccessLevel(User_Id) From Users
Order by MaxDate desc
dbo.getmaxDate and dbo.GetMaxAccessLevel are 2 functions which will accept user id and get the date and access level
October 6, 2008 at 11:56 am
OK, well at the risk of sending this thread spiraling into 2k5 VB.net land and hoping theres a few here with knowledge in both.
First the Access level was just for flavor. The ISO statutes my company is under prevent me from really discussing even the actual field names of the DB.
What I need to Hinge on is the "Start_Date" and User_ID. The User_ID must be distinct and return the row that has the latest "Start_Date".
I will admit you start talking about stored procedures and user defined functions in SQL my eyes get a little blurry.
The Jist of what im trying to do is this.
Pull in the above described records into a dataview (aka record set). then cycle through this data view checking to see if the User_ID exists in another remote host database. If it exists it gets updated with very specific info from the source DB.(Where these distinct rows came from). If they dont exist then INSERT the data into the destination DB.
Problem Im having is this. My statement is bringing in ALL of the USER_IDs over. If its possible I just need a SQL statement that can produce the rows i need and not the ones i dont.
Can it be done or do I need to solve this programmatically?
October 6, 2008 at 12:30 pm
How about something like this?
select < column list > from users inner join
(SELECT User_ID, MAX(Start_Date) as LatestStartDate FROM Users group by User_ID) sub
on Users.User_ID = sub.User_ID and Users.Start_Date = sub.LatestStartDate
As for typing the column names. No need. Open object explorer, connect to this server, expand out databases and tables and find this table. Expand that out and drag the columns folder to the query window. Voilà, a comma-delimited column list, no typing necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2008 at 12:43 pm
GilaMonster (10/6/2008)
How about something like this?
select < column list > from users inner join(SELECT User_ID, MAX(Start_Date) as LatestStartDate FROM Users group by User_ID) sub
on Users.User_ID = sub.User_ID and Users.Start_Date = sub.LatestStartDate
As for typing the column names. No need. Open object explorer, connect to this server, expand out databases and tables and find this table. Expand that out and drag the columns folder to the query window. Voilà, a comma-delimited column list, no typing necessary.
*Ladies and Gents thats right! She's all that and brains too!!!!!!*
GAIL YOU ROCK MY SOCKS!!!!!
Thanks for saving my bacon. I owe u one.
October 6, 2008 at 12:49 pm
Or you can do something like this in SQL Server 2005/2008. (Pardon my use of the * in the query).
create table dbo.MyUsers (
UserID varchar(25),
FirstName varchar(25),
LastName varchar(25),
StartDate datetime,
AccessLeve smallint
);
insert into dbo.MyUsers
select 'Jdoe','John','Doe','1/1/1900',1 union all
select 'Jdoe','John','Doe','10/9/2007',3 union all
select 'BSmith','Bob','Smith','1/1/2000',2 union all
select 'BSmith','Bob','Smith','9/1/2006',4 union all
select 'SJones','Sally','Jones','10/10/1950',5 union all
select 'SJones','Sally','Jones','10/10/2004',2;
with EffectiveUser (
UserID,
StartDate
) as (
select
UserID,
max(StartDate)
from
dbo.MyUsers
group by
UserID
)
select
*
from
dbo.MyUsers mu
inner join EffectiveUser eu
on (mu.UserID = eu.UserID
and mu.StartDate = eu.StartDate);
drop table dbo.MyUsers;
😎
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply