July 27, 2009 at 6:31 am
I have 2 tables problem and problem notes
problem table:
rowid |lognumber |username |dateraised |dateresolved
1 |3 |dbrooks |24/05/2007 |25/05/2007
problem note table:
|rowid |lognumber |lognotdate |log_notes
|1 |3 |25/05/2007 |User logged a complain
|2 |3 |25/05/2007 |PC not working
|3 |3 |29/05/2007 |Pls help with browser problem
After joining the 2 tables ON lognumber how can i get a top 1 / max of the 2 tables( the max after grouping)
I want a result like this:
rowid lognumber username dateraised logneotedate log_notes
3 3 dbrooks 24/05/2007 29/05/2007 Pls help with browser problem
Thanks in Advance
July 27, 2009 at 6:42 am
Top or max according to which column?
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
July 27, 2009 at 7:03 am
Gail rowid or lognotedate
July 27, 2009 at 7:14 am
Untested.
SELECT rowid, lognumber, username, dateraised, logneotedate, log_notes
FROM
(SELECT ProblemNote.rowid, Problem.lognumber, username, dateraised, logneotedate, log_notes,
ROW_NUMBER OVER (Partition By Problem.lognumber ORDER BY ProblemNote.rowid DESC) AS RowNo
FROM Problem Inner Join ProblemNote on Problem.lognumber = ProblemNote.lognumber) s
WHERE RowNo = 1
That'll give you the max row per lognumber. If you want the max overall, remove the "Partition By Problem.lognumber"
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
July 27, 2009 at 9:10 pm
After trying the below i get error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "problem_log_notes.log_notes" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "problem_log_notes.log_note_date" could not be bound
If i remove the problem_log_notes.log_notes,problem_log_notes.log_note_date
from the selct it does work but as i said i need thos 2 columns from the second table
Thanks
SELECT row_id, log_number, username, date_raised,problem_log_notes.log_notes,problem_log_notes.log_note_date
FROM
(SELECT problem_log_notes.row_id, problem_log.log_number, problem_log.username, problem_log.date_raised,
ROW_NUMBER() OVER (Partition By problem_log.log_number ORDER BY problem_log_notes.row_id DESC) AS RowNo
FROM problem_log Inner Join problem_log_notes on problem_log.log_number = problem_log_notes.log_number) s
WHERE RowNo = 1
July 28, 2009 at 1:00 am
All columns referenced in the outer select have to be referenced in the inner select as well. The columns in the outer select must not have table names, as the tables are no longer visible at that point, just the derived table that's called s
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
July 28, 2009 at 5:06 am
Use this query. i dont remeber table names so using sample query
Select top 1 [column list]
from table1 inner joni table2 on table1.column2=table2.column2
order by table2.date desc
if you want latest record from second table with respect to every record in first table then use.
with result
(
Select rank() over (partition by table2.column2 order by table2.date) rowid,
[column list]...
from table1 inner joni table2 on table1.column2=table2.column2
)
select * from result where rowid = 1
)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply