Search Results

Search found 29421 results on 1177 pages for 'sql newbie'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 346/1177 | < Previous Page | 342 343 344 345 346 347 348 349 350 351 352 353  | Next Page >

  • SQL query: Last but one rank for user

    - by Derk
    My table structure looks like this: create table rankings ( id IDENTITY NOT NULL, user_id INT NOT NULL, game_poule_id INT NOT NULL, rank INT NOT NULL, insertDate DATETIME NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (game_poule_id) REFERENCES game_poules(id) ON DELETE CASCADE ); All old rankings of users per game are saved in this table. Now I want to have the last but one rank in the table for all users in a gamepoule. Has someone an idea how to achive this? Thanks

    Read the article

  • merging two tables, while applying aggregates on the duplicates (max,min and sum)

    - by cloudraven
    I have a table (let's call it log) with a few millions of records. Among the fields I have Id, Count, FirstHit, LastHit. Id - The record id Count - number of times this Id has been reported FirstHit - earliest timestamp with which this Id was reported LastHit - latest timestamp with which this Id was reported This table only has one record for any given Id Everyday I get into another table (let's call it feed) with around half a million records with these fields among many others: Id Timestamp - Entry date and time. This table can have many records for the same id What I want to do is to update log in the following way. Count - log count value, plus the count() of records for that id found in feed FirstHit - the earliest of the current value in log or the minimum value in feed for that id LastHit - the latest of the current value in log or the maximum value in feed for that id. It should be noticed that many of the ids in feed are already in log. The simple thing that worked is to create a temporary table and insert into it the union of both as in Select Id, Min(Timestamp) As FirstHit, MAX(Timestamp) as LastHit, Count(*) as Count FROM feed GROUP BY Id UNION ALL Select Id, FirstHit,LastHit,Count FROM log; From that temporary table I do a select that aggregates Min(firsthit), max(lasthit) and sum(Count) Select Id, Min(FirstHit),Max(LastHit),Sum(Count) FROM @temp GROUP BY Id; and that gives me the end result. I could then delete everything from log and replace it with everything with temp, or craft an update for the common records and insert the new ones. However, I think both are highly inefficient. Is there a more efficient way of doing this. Perhaps doing the update in place in the log table?

    Read the article

  • Multi table Triggers SQL Server noob

    - by Chin
    I have a load of tables all with the same 2 datetime columns (lastModDate, dateAdded). I am wondering if I can set up global Insert Update trigger for these tables to set the datetime values. Or if not, what approaches are there? Any pointers much appreciated

    Read the article

  • SQL Stored Procedure

    - by Nathan
    I am trying to run a stored procedure with a while loop in it using Aqua Data Studio 6.5 and as soon as the SP starts Aqua Data starts consuming an increasing amount of my CPU's memory which makes absolutely no sense to me because everything should be off on the Sybase server I am working with. I have commented out and tested every piece of the SP and narrowed the issue down to the while loop. Can anyone explain to me what is going on? create procedure sp_check_stuff as begin declare @counter numeric (9), @max_id numeric (9), @exists numeric (1), @rows numeric (1) select @max_id = max(id) from my_table set @counter = 0 set @exists = 0 set @rows = 0 while @count <= @max_id begin //More logic which doesn't affect memory usage based //on commenting it out and running the SP set @counter = @counter + 1 set @exists = 0 set @rows = 0 end end return

    Read the article

  • performing auditing in java with sql server DB - before and/or after do not get audited

    - by Domingos
    When auditing, sometimes the before value does not get audited, other times the after value does not get audited, other times both values do not get audited at all. After researching, I found out that only values from a specific codes table get audited. the code was: compareCodesTableInteger(audit, int, int, objectBefore, objectAfter, stringDescription, stringCodesTable); I then changed it to: compareCodesTableInteger(audit, int, int, objectBefore, objectAfter, stringDescription, booleanCheck ? stringCodesTableIfTrue : stringCodesTableIfFalse); Description: if objectBefore AND objectAfter are both from stringCodesTableIfTrue OR from stringCodesTableIfFalse, auditing takes place as expected. The problem is: most of the times, objectBefore is from stringCodesTableIfTrue, and objectAfter is from stringCodesTableIfFalse, or vice-versa. In this scenario auditing fails. How do I go around this? Please assist

    Read the article

  • Simplest LINQ in C# troubleshooting.

    - by Jay
    I'm trying to learn a bit of LINQ but I'm having compile issues right off the bat. Is there any specific reason why this won't work? using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace HelloLINQ { class HelloLINQ { public static void Main() { Example1(); } public static void Example1() { var numbers = new int[] { 1, 5, 3, 7, 3, 8, 9, 3, 6, 6, 2 }; var under5 = from n in numbers select n; foreach (var n in under5) { Console.WriteLine(n); } } } } The error is: Could not find an implementation of the query pattern for source type 'int[]'. 'Select' not found. Are you missing a reference to 'System.Core.dll' or a using directive for 'System.Linq'?

    Read the article

  • Help with SQL query in C#

    - by DanSogaard
    I'm trying to rename the columns. The syntax should be the column name between double quotes incase of two words, like this: SELECT p_Name "Product Name" from items So I'm trying to do it in C# code like this: string sqlqry1 = "SELECT p_Name \"Prodcut Name\" from items"; But I get an error: Syntax error (missing operator) in query expression 'p_Name "Prodcut Name"'. It seems am having somthing wrong with the quotes, but I can't figure out.

    Read the article

  • Convert Military time to string representation

    - by RRUZ
    I have an column declarated as int (called HourMil) wich store the time in military format. i need convert this values to an formated string (HH:MM) example HourMil = 710 -> must be 07:10 HourMil = 1305 -> must be 13:05 Actually i am using this code (and works ok) for convert the column HourMil to the string representation. SELECT SUBSTRING(LEFT('0',4-LEN(CAST(HourMil AS VARCHAR)))+CAST(HourMil AS VARCHAR),1,2)+':'+SUBSTRING(LEFT('0',4-LEN(CAST(HourMil AS VARCHAR)))+CAST(HourMil AS VARCHAR),3,2) FROM MYTABLE but I think this code can be improved.

    Read the article

  • I need some ideas on my algortihm for a Hit Counter

    - by stckvrflw
    My algorithm is for a hit count, I am tring to not count for the same person twice if that person came to the site twice in a time internval (For example if he comes twice in 5 minutes, I want to count it as 1 for this person) Here how my database looks like UserIp UserId Date of user came 127.0.0.1 new.user.akb 26.03.2010 10:15:44 127.0.0.1 new.user.akb 26.03.2010 10:16:44 127.0.0.1 new.user.akb 26.03.2010 10:17:44 127.0.0.1 new.user.akb 26.03.2010 10:18:44 127.0.0.1 new.user.akb 26.03.2010 10:19:44 127.0.0.1 new.user.akb 26.03.2010 10:20:44 127.0.0.1 new.user.akb 26.03.2010 10:21:44 127.0.0.1 new.user.akb 26.03.2010 10:22:44 127.0.0.1 new.user.akb 26.03.2010 10:23:44 What I need to do is get number of distinct UserIPs from the table above that occured within a time interval. For example if I set the time interval for 5 minutes, and let say that is starts at 26.03.2010 10:15:44 Then I will get 2 as the results, since 1 distinct value between 10:15 to 10:20 and , 1 distinct value from 10:20 to 10:23, For example if my interval is 3 minutes than the return result will be 3 Thanks.

    Read the article

  • Reading a part of a alpha numeric string in SQL

    - by novice
    I have a table with one column " otname " table1.otname contains multiple rows of alpha-numeric string resembling the following data sample: 11.10.32.12.U.A.F.3.2.21.249.1 2001.1.1003.8281.A.LE.P.P 2010.1.1003.8261.A.LE.B.B I want to read the fourth number in every string ( part of the string in bold ) and write a query in Oracle 10g to read its description stored in another table. My dilemma is writing the first part of the query.i.e. choosing the fourth number of every string in a table My second query will be something like this: select description_text from table2 where sncode = 8281 -- fourth part of the data sample in every string Many thanks. novice

    Read the article

  • Trying to use table aliases in SQL...

    - by user366685
    I'm a graphic designer trying my best to understand table aliases, but it's not working. Here's what I have so far: SELECT colours.colourid AS colourid1, combinations.manufacturercolourid AS colourmanid1, colours.colourname AS colourname1, colours.colourhex AS colourhex1, combinations.qecolourid2 AS colouridqe2, colours.colourid AS colourid2, colours.colourname AS colourname2, colours.colourhex AS colourhex2, colours.colourid AS colourid3, combinations.qecolourid3 AS colouridqe3, colours.colourname AS colourname3, colours.colourhex AS colourhex3, colours.colourid AS colourid4, combinations.qecolourid4 AS colouridqe4, colours.colourname AS colourname4, colours.colourhex AS colourhex4, combinations.coloursupplierid FROM combinations INNER JOIN colours ON colours.colourid = combinations.manufacturercolourid; Now, the idea is that in the colours lookup table, the id will pull the colour code, hex and name from the lookup table so that I can pull the colour code, hex and name for the 4 colours that I'm looking for. I can get this to work, but it only pulls up the first name, code and hex and I'm just not seeing what I'm doing wrong.

    Read the article

  • T-SQL Getting duplicate rows returned

    - by cBlaine
    The following code section is returning multiple columns for a few records. SELECT a.ClientID,ltrim(rtrim(c.FirstName)) + ' ' + case when c.MiddleName <> '' then ltrim(rtrim(c.MiddleName)) + '. ' else '' end + ltrim(rtrim(c.LastName)) as ClientName, a.MISCode, b.Address, b.City, dbo.ClientGetEnrolledPrograms(CONVERT(int,a.ClientID)) as Abbreviation FROM ClientDetail a JOIN Address b on(a.PersonID = b.PersonID) JOIN Person c on(a.PersonID = c.PersonID) LEFT JOIN ProgramEnrollments d on(d.ClientID = a.ClientID and d.Status = 'Enrolled' and d.HistoricalPKID is null) LEFT JOIN Program e on(d.ProgramID = e.ProgramID and e.HistoricalPKID is null) WHERE a.MichiganWorksData=1 I've isolated the issue to the ProgramEnrollments table. This table holds one-to-many relationships where each ClientID can be enrolled in many programs. So for each program a client is enrolled in, there is a record in the table. The final result set is therefore returning a row for each row in the ProgramEnrollments table based on these joins. I presume my join is the issue but I don't see the problem. Thoughts/Suggestions? Thanks, Chuck

    Read the article

  • reclaim unsued space in sql 2008

    - by opensas
    I have a table with more than 300.000 records, that weights approximately 1.5 GB In that table I have three varchar(5000) fields, the rest are small fields I issue an update, setting those three fields to '' An after a shrink (database and files) the database uses almost the same space as before... DBCC SHRINKDATABASE(N'DataBase' ) DBCC SHRINKFILE (N'DataBase' , 1757) DBCC SHRINKFILE (N'DataBase_log' , 344) any idea?

    Read the article

  • Creating a stored procedure in SQL Server 2008 that will do a "facebook search"

    - by dig
    Hello, I'm trying to implement a facebook search in my system (auto suggest while typing). I've managed to code all the ajax stuff, but I'm not sure how to query the database. I've created a table called People which contains the fields: ID, FirstName, LastName, MiddleName, Email. I've also created a FTS-index on all those fields. I want to create a stored procedure that will get as a parameter the text inserted in the query box and returns the suggestions. For example, When I will write in the textbox the query "Joh Do" It will translate to the query: select * from People where contains(*, '"Joh*"') and contains(*, '"Do*"') Is there a way to do that in stored procedure? P.S I've tried to use the syntax select * from People where contains(*,'"Joh*" and "Do*"') but it didn't returned the expected results, probably because it needs to search the words on different fields. Is there a way to fix that? Thanks.

    Read the article

  • Inner join 2 tables one to many 2 where clauses

    - by user2892350
    I'm a relative rookie at this,so please bear with me... I have 2 tables: OrderDetail and OrderMaster...both have a column named SalesOrder. OrderDetail table has multiple rows per unique SalesOrder. OrderMaster table has one row per unique SalesOrder. OrderDetail has a column named LineType. OrderMaster has a column named OrderStatus. I want to select all records from OrderDetail that have a LineType of "1" AND whose matching SalesOrder line in the OrderMaster table has a OrderStatus column value of "4". In plain English, orders with a Status 4 are open and ready to ship, LineType value of 1 means the Detail Line is a product code. How should this query be structured? It's going into VS 2008 (VB). Many thanks in advance!!! Mike

    Read the article

  • Modeling many-to-one with constraints?

    - by Greg Beech
    I'm attempting to create a database model for movie classifications, where each movie could have a single classification from each of one of multiple rating systems (e.g. BBFC, MPAA). This is the current design, with all implied PKs and FKs: TABLE Movie ( MovieId INT ) TABLE ClassificationSystem ( ClassificationSystemId TINYINT ) TABLE Classification ( ClassificationId INT, ClassificationSystemId TINYINT ) TABLE MovieClassification ( MovieId INT, ClassificationId INT, Advice NVARCHAR(250) -- description of why the classification was given ) The problem is with the MovieClassification table whose constraints would allow multiple classifications from the same system, whereas it should ideally only permit either zero or one classifications from a given system. Is there any reasonable way to restructure this so that a movie having exactly zero or one classifications from any given system is enforced by database constraints, given the following requirements? Do not duplicate information that could be looked up (i.e. duplicating ClassificationSystemId in the MovieClassification table is not a good solution because this could get out of sync with the value in the Classification table) Remain extensible to multiple classification systems (i.e. a new classification system does not require any changes to the table structure)? Note also the Advice column - each mapping of a movie to a classification needs to have a textual description of why that classification was given to that movie. Any design would need to support this.

    Read the article

  • Incorrect syntax near the keyword 'select' while execuing query

    - by sam
    I am getting Incorrect syntax near the keyword 'select' after executing the following code. declare @c int SELECT @c = COUNT(*) FROM (select id, max(date_stored) from table B INNER JOIN table P ON B.id = P.id where id = 3) select @c I want to select total no of records having max stored dates in database. Can any one plz tell what I am doing wrong

    Read the article

  • integrity Constraints on a table.

    - by Dinesh
    See this sample schema Passenger(id PK, Name) Plane(id PK, capacity, type); Flight(id PK, planeId FK(Plane), flightDate, StartLocation, destination) CREATE TABLE Reservation(PassengerId, flightId, PRIMARY KEY (passengerId, flightId), FOREIGN KEY (passengerId) REFERENCES Passenger, FOREIGN KEY (flightId) REFERENCES Flight); I need to define an integrity constraint that enforces the restriction that the number of passengers on a plane cannot exceed the plane’s capacity. I have tried and achieved so far is this. CREATE TABLE Reservation( passengerId INTEGER, flightId INTEGER, PRIMARY KEY (passengerId, flightId), FOREIGN KEY (passengerId) REFERENCES Passenger, FOREIGN KEY (flightId) REFERENCES Flight, Constraint check1 check(Not Exists(select * from Flight s, (select count(*) as totalRes from Reservation group by flightId) t where t.totalRes > s.capacity ) ) ); I am not sure i am doing in right way or not. Any suggestions?

    Read the article

  • Application self aware of external database record modifications.

    - by Khou
    How do you make your application aware that a database record was changed or created by an external application rather than the application itself? Do you add a special check sum to the database record or what do you do to stop external changes to the database? (in case it was hacked and the hacker decides to add a new record or change an existing database record)

    Read the article

  • Cannot resolve collation conflict in Union select

    - by phenevo
    Hi, I've got tqo queries: First doesn't work: select hotels.TargetCode as TargetCode from hotels union all select DuplicatedObjects.duplicatetargetCode as TargetCode from DuplicatedObjects where DuplicatedObjects.objectType=4 because I get error: Cannot resolve collation conflict for column 1 in SELECT statement. Second works: select hotels.Code from hotels where hotels.targetcode is not null union all select DuplicatedObjects.duplicatetargetCode as Code from DuplicatedObjects where DuplicatedObjects.objectType=4 Structure: Hotels.Code -PK nvarchar(40) Hotels.TargetCode - nvarchar(100) DuplicatedObjects.duplicatetargetCode PK nvarchar(100)

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 342 343 344 345 346 347 348 349 350 351 352 353  | Next Page >