July 31, 2018 at 10:25 am
I'm working on combining the latest record from a number of tables to create one view with the most current data from all of the tables.
The first bunch of tables I had no problem because there is one row from each table but there are 3 other tables I need to include which will have multiple records returned as the latest record.
So far this is what I have:
SELECT *
FROM (SELECT * FROM Settings WHERE dateCreated = (SELECT MAX(datecreated) maxSettings FROM Settings)) settings,
(SELECT * FROM Defaults WHERE dateCreated = (SELECT MAX(datecreated) maxDefaults FROM Defaults)) DEFAULTS,
(SELECT * FROM Dashboard WHERE dateCreated = (SELECT MAX(datecreated) FROM Dashboard)) DASHBOARD
and I need to include the results from these queries which are the tables that will have more than one record returned as the latest:
To get the latest record from these tables I would use:
SELECT MAX(datecreated) maxroledate, OfficeRoleNum FROM OfficeRoles GROUP BY OfficeRoleNum
SELECT MAX(datecreated) maxCFDate, CustomFlagNum FROM CustomFlags GROUP BY CustomFlagNum
SELECT MAX(datecreated) maxCCFDate, CustomClosingFlagNum from CustomClosingFlags GROUP BY CustomClosingFlagNum
I am trying to avoid writing a query for each record such as
select * from OfficeRoles
where OfficeRoleNum = 1 and dateCreated = (SELECT MAX(datecreated) maxroledate FROM OfficeRoles where officeRoleNum = 1)
union
Select * from OfficeRoles
where OfficeRoleNum = 2 and dateCreated = (SELECT MAX(datecreated) maxroledate FROM OfficeRoles where OfficeRoleNum = 2)
Really appreciate any ideas on how to combine these
July 31, 2018 at 10:39 am
Maybe using OVER and PARTITION BY might work?
http://www.sqlservercentral.com/articles/Over+Clause/132079/
July 31, 2018 at 10:44 am
If you need to combine the results into a single set of results, you can use UNION ALL, and as such requires the same number of columns and matching data types, for any given query, you NULL out any other column names needed for the other queries.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 31, 2018 at 10:50 am
Oh good point. That will return a table of all the latest values.
July 31, 2018 at 11:04 am
yoyodynedata - Tuesday, July 31, 2018 10:50 AMOh good point. That will return a table of all the latest values.
Glad I could help....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 31, 2018 at 11:11 am
cchapel - Tuesday, July 31, 2018 10:39 AMMaybe using OVER and PARTITION BY might work?
http://www.sqlservercentral.com/articles/Over+Clause/132079/
I can do it with:
Select OfficeRoles.OfficeRoleName, OfficeRoles.OfficeRoleNum
from OfficeRoles
join (SELECT ROW_NUMBER() OVER (PARTITION BY officeroleNum ORDER BY dateCreated desc) recNum, OfficeRoleNum, dateCreated FROM OfficeRoles) currentRec
ON currentRec.dateCreated = BR_OfficeRoles.dateCreated AND currentRec.OfficeRoleNum = BR_OfficeRoles.OfficeRoleNum AND recNum = 1
This returns a table of all the latest values with their roleNum.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply