I have been asked in an interview to write a SQL query which fetches the first three records with highest value on some column from a table. I had written a query which fetched all the records with highest value, but didn't get how exactly i can get only first three records of those. Could you help me in this. Thanks. ...
Environment: Jboss, Mysql, JPA, Hibernate Our web application will be catering to a large amount of users (~ 1,000,000) and there are a lots of child table where user specific data are stored (e.g. personal, health, forum contributions ...). What would be the best practice to archive user & user specific information. [a] Would it be...
update answers a1 set a1.FilingDate=( select date_sub( a2.FilingDate ,Interval 1000 Year) as FilingDate from answers a2 where Year(a2.FilingDate)>=3000 ) where Year(a1.FilingDate)>=3000 but it gives me t...
Hi all, I am having a common field in ten tables with different field name. example: table1: t1_id t1_location 1 india 2 china 3 america table2: t2_id t2_location 4 london 5 australia 6 america Now my...
Hi. Does SQL Server maintains any history to track table alterations like column add, delete, rename, type/ length change etc? I found many suggest to use stored procedures to do this manually. But I'm curious if SQL Server keeps such history in any system tables? Thanks. ...
Last night I came up to sometihng intersting while designing my new project that brought me to ask this qustion here. My project is supposed to follow Table Gateway pattern using tradional ADO.Net datasets for data access. I don't want to write plain queries in my data-access classes. So I came up with an idea of writing a parser kinda...
I have a very simple CLR Function for doing Regex Matching public static SqlBoolean RegExMatch(SqlString input, SqlString pattern) { if (input.IsNull || pattern.IsNull) return SqlBoolean.False; return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase); } It allows me to write a SQL Statement Like. SEL...
create or replace procedure createtables Authid current_user as begin execute immediate 'create table newcustomer as select * from customer'; end; create or replace procedure e is begin createtables; select * from newcustomer; end; I got two procedures above. first one will create a new tables called newcustomer, second procedu...
Hui folks, I need to be able to store numbers like 3.5 in my table. So I've used the decimal type field. But if I enter 3.5 it round it up to 4. Am I being stupid or is it not the point of decimal to have a decimal point? Thanks, Jonesy ...
I have a number of .sql files which I have to run in order to apply changes made by other developers on an SQL Server 2005 database. The files are named according to the following pattern: 0001 - abc.sql 0002 - abcef.sql 0003 - abc.sql ... Is there a way to run all of them in one go? ...
Hi there, I'm not sure if this is possible what I'm trying to achieve. I want to get the avg of averaged columns. SELECT avg(col1), avg(col2), avg(col3) FROM tbl My Result should be the avg of all three avg columns, is this possible? Something like this SELECT avg( col1, col2, col3) FROM tbl doesn't work at MySQL 5.1 ...
I've a document search page with three listboxes that allow multiple selections. They're: Category A Year Category B Only category A is mandatory, the others are optional parameters and might be empty. Each document can belong to multiple options in Category A and multiple options Category B but each document only has one year assoc...
i want to know what wordpress build-in function you will use when sanitizing specific input ? for example an sql query, numeric, etc. ...
I am trying to right-align the output of some PL/SQL code, but dbms_output.put_line is trimming the leading whitespace from my strings. How do I make it stop? Or is there a better way to output strings with leading whitespace? dbms_output.put_line(lpad('string', 30, ' ')); outputs: string instead of: string...
I've got column Cars with column "Owner". I has one owner , and he has 10 cars. Now I've 2 additional person who has the same cars, so I need Add 20 records to my table. Wiht only one column different: Something like: Insert into Cars (NameOfCar,NameOfOwner) Select NameOfCar,'"Robert Kubica' Where NameOfOwner='Schumacher'. Insert...
FORMSOF THESAURUS throws errors for specific character. E.g. FORMSOF (THESAURUS, hel!lo) throws an error whereas FORMSOF (THESAURUS, hel?lo) works. However, I did not find any documentation about which characters are allowed. Can you help me out? ...
Hi I am creating a stored procedure to which I want to pass as variable a comma delimited list of Ids. I want to use the Ids into a select statement, something like: Create Procedure up_TEST @Ids VARCHAR(MAX) AS SELECT * FROM ATable a WHERE a.Id IN(@Ids) Obviously I get the error that @Ids is a varchar and not an INT but how can I...
I have a lot of SQL queries like this: SELECT o.Id, o.attrib1, o.attrib2 FROM table1 o WHERE o.Id IN ( SELECT DISTINCT Id FROM table1 , table2 , table3 WHERE ... ) These queries have to run on different database engines (M...
I am doing a SQL Insert to populate my table. I have a unique generated ID in one table that I would like to use in another table for my join. Is this possible? .NET MVC -- using (SqlConnection connect = new SqlConnection(connections)) { SqlCommand command = new SqlCommand("ContactInfo_Add", connect); command.Parameters.Add(n...
I have a view retrieving data like Course Attendee if-student shadow-tutor course-max CS1 steve no mark 5 CS1 anna yes dan 5 and after retrieving, I select the course, count(attendee), shadow-tutor, course-max where if-student = Parameters!if_student. The result would be like: C...