Rather than grouping, go about it like this… select * from account a join ( select account_id, row_number() over (order by account_id, id) – rank() over (order by account_id) as row_num from user ) first on first.account_id = a.id and first.row_num = 0
SELECT * FROM sys.databases
I finally figured it out. It was buried in the comments to the article jfrobishow published. Thank you SO much. Here is the whole response that led me to the solution: quote:Originally posted by fredclown If you use SQL 2005 you can use replace with a text type. All you have to do is the … Read more
In case you still need to know how to do this. Edit the Registry Key in HKEY_CURRENT_USER: Software\Microsoft\Microsoft SQL Server\xx\Tools\Shell\Text Editor\Visible Whitespace Where xx is the version you have (90 is 2005, 100 is 2008) And set it to 1 to show. Or for SQL Server 2012 and up client tools it is also in … Read more
Try this: SELECT *, ROW_NUMBER() OVER(ORDER BY Id) ROW_NUM FROM ( select Id, VersionNumber from documents where id=5 Union all select Id, VersionNumber from versions where id=5 ) a order by VersionNumber desc TO filter by version number 5 use: SELECT * FROM (SELECT *, Row_number() OVER(ORDER BY versionnumber DESC, id) row_num FROM (SELECT id, … Read more
You can do this simply without the DECLARE command – which is not valid for #temp tables anyway, only @table variables. Did you try just the following without trying to define #tmp first: SELECT * INTO #tmp FROM myTable;
select @currentTerm = CurrentTerm, @termID = TermID, @endDate = EndDate from table1 where IsCurrent = 1
Assuming this is actually a composite key, since you can’t have 2 primary keys… The error message tells you exactly what to do, namely add an order. You can do this by adding [Column(Order = 0)] and [Column(Order = 1)] to your key columns. For your example: public class LineItem { [Key][Column(Order = 0)] public … Read more
;with cteRowNumber as ( select COMPUTERNAME, SERIALNUMBER, USERNAME, LASTIP, LASTUPDATE, SOURCE, row_number() over(partition by COMPUTERNAME order by LASTUPDATE desc) as RowNum from YourTable ) select COMPUTERNAME, SERIALNUMBER, USERNAME, LASTIP, LASTUPDATE, SOURCE from cteRowNumber where RowNum = 1
You can get the difference between the two dates to whatever resolution you want (in your example, minutes): DATEDIFF(minute, @start_date, @end_date) From there it’s a simple matter of dividing minutes into hours and hours into days and modding the remainder.