New answers tagged

0

Physical order of records is defined by the clustered index, if it exists. Whatever you requested in a SQL query - it will be returned to you by the server, it is just matter of how many pages in files the server will have to look through in order to find what you requested. There is no single answer on your question, because the answer might be different ...


0

The simplest way to select the last row (by bill_dt) in each group of rows with the same carrier_cd in Teradata would probably be using QUALIFY: QUALIFY 1 = ROW_NUMBER() OVER (PARTITION BY carrier_cd ORDER BY bill_dt DESC) If you're sure there are no ties (i.e. there's only one row with bill_dt = MAX(bill_dt) for each carrier_cd), you could calculate the ...


0

It's a pain, but Teradata doesn't support CTE in views (as of 15.00), see SQL Data Manipulation Language > The SELECT Statement > WITH and WITH RECURSIVE Statement Modifiers. In your case you can create another view with the contents of the CTE, but you probably know that already.


1

A case for window functions: SELECT count(*) As longest_streak FROM ( SELECT row_number() OVER w - count(status = 'WON' OR NULL) OVER w AS grp FROM game WHERE player_id = 1 -- %some id% WINDOW w AS (ORDER BY date) ) sub GROUP BY grp ORDER BY count(*) DESC LIMIT 1; Or use the aggregate FILTER clause in Postgres 9.4 ...


1

You can use ROW_NUMBER, like this: SELECT ROW_NUMBER()OVER(ORDER BY ID ASC) AS [Count], body FROM @Article AS A WHERE authorid=1 Count body 1 Hi 2 there 3 foo


1

There's no replication or out-of-the-box solution to your problem, particularly since you don't have that type of access to the remote machine. As other commenters here have noted, you're probably going to have to build your own ETL logic, perhaps in one of the following ways: A linked server and stored procedures using OPENQUERY() or OPENROWSET(), An SSIS ...


5

As long as they are not nested, you should be fine. @@FETCH_STATUS is set at the time the FETCH NEXT is performed. @@FETCH_STATUS is global to the session, not the entire server. There is no equivalent to SCOPE_IDENTITY() for @@FETCH_STATUS. If you need to nest looped FETCH NEXT operations, make sure that your outer FETCH NEXT is performed after the inner ...


1

I think that this is a business-driven question that your client/management should decide. Most well-designed databases can scale a lot larger than you may think at first. It's good that you're thinking of an archiving strategy, but you should really only build this on an actual, outspoken business requirement. I'm almost certain that with a little index ...


0

Not quite sure about what you're after, but you want a list that is created (or not) by one user and this list can have multiple items. The votes on each of the item will determine what position they are on the list? I can think of it like this: The table Lists contains the data of the list itself and the table ListItems contains the ListID, the Item and ...


1

The question is all over the place but I think this is what you are looking for select a.name, u.name, b.note, b.date from table_B as B join table_A as u on u.ID = B.ID join table_A as a on a.ID = B.admin_id where B.ID = @userID you can create a line with select a.name + " took action on " + u.name ...


0

I think I'm understanding basically what you're after. Are you wanting to record each individual vote or maintain a single value in a +1/-1 fashion? This could be a start anyway: UserLists Table Userid ListId VotingAllowed CreationDate ListItems Table ListId ListDescription InitialSequence Votes


0

You need at least an index on FK_AuftragPos.AuftragsNr, without the plan is a bit difficult but I think you need it for the joins


0

For lack of information, assuming created_at is data type timestamp, saving UTC timestamps: SELECT camera_id, count(*) AS snapshot_count FROM snapshots WHERE created_at >= timestamp '2015-01-01' -- From date: 2015/01/01 AND created_at < timestamp '2015-12-31' -- to date: 2015/12/30 -- chop off ...


1

I doubt if there is a transaction id. But here are some "good practices": Turn off auto-reconnect. (This is a parameter on certain APIs when establishing the connection.) A disconnect terminates transactions and rolls them back. With auto-reconnect OFF, you will get an error on whatever happens next. Check for errors after ever SQL statement. Avoid ...


0

To retrieve data from both table associated with foreign key i.e(common column) you have to join both the tables. if you matching data from both table then use INNER JOIN. > SELECT A.List_Of_columns,B.List_Of_columns FROM Table1 AS A INNER > JOIN Table2 as B ON A.ID=B.ID (Here Id is Common in both table). If you want matching data from both ...


0

For getting the result from the two table associated with the foreign key, you need to join the tables. Find below query as like you want the result: SELECT name, date, roll FROM A INNER JOIN B ON A.id = B.id;


2

You need to join these tables to get the result that you want. SELECT b.*, a.name FROM tableB AS b INNER JOIN tableA as A ON (b.id=a.id); This query will return everything from Table B and name from Table A where the ID from Table B is the same as the ID from Table A.


3

Joining tables is a fundamental principle of relational databases. In your case, A and B are related with the id column, which means that you can use a syntax similar to this one: SELECT a.id, a.name, a.num, b.date, b.roll FROM a INNER JOIN b ON a.id=b.id; INNER JOIN means that you'll only see rows where there are matching records in A and B. If you want ...


3

Most likely because the expression 1/10/1971 is interpreted as a numeric expression, i.e. 1 divided by 10 divided by 1971 which is 0 (with integer division rounding values down). Telling Access that this is a date can be done using quotes (not really recommended) or by using a date function, like this: IIf([DOB]<DateSerial(1971, 10, 1), "Mature", "Not ...


3

Normalizing this will result in three tables with an many:many relation, the columns in parens () indicate the logical Primary Key: groups: (group_id) ingredients: (ingredient_id), name ingredients_per_group: (group_id, ingredient_id), position This is the junction table between groups and ingredients, the position is just an attribute of the ingredient ...


3

You cannot use the CASE expression in DB2 this way. The result of a CASE expression cannot be a boolean value. Your WHERE clause might look something like this, if one were to blindly translate your code: WHERE COUNTRY_CD = '81930' AND LANG_CD = '02' AND ( ( PARM_ADTR_ID = 'ALL' AND (AS_ADTR_ID_P IS NULL OR AS_ADTR_ID_P LIKE '%') ) ...


0

Solution of your problem- SELECT CMSTKY AS Customer, CMNAME AS BusinessName, SUM(WKSLD#) AS LYSales FROM GPCMP INNER JOIN GPCM1P ON GPCMP.CMSTKY = GPCM1P.C1STKY INNER JOIN GMP725WP ON GPCMP.CMSTKY = GMP725WP.WKSTKY WHERE WKTERR = '055' AND WKSLD# != 0 GROUP BY CMSTKY, CMNAME


1

There is an error in this line: between 's.proddate 22:30:00' and 'DATE_ADD(s.proddate,INTERVAL 1 DAY) 02:30:00' It should be: select count(*) as t, m.machine1 from sap_prod_data s left join sap_machine_speed m on s.mch_desc = m.machine where year(s.proddate) = '2016' and m.machine1 in ('bla','bla') and s.total_down_time>30 and ...


0

I don't think it's possible to give any definitive answer of the future of the big RDBMs players. If the subject does interest you, you could do worse than have a look at: http://db-engines.com/en/ranking_trend http://www.dbta.com/ Just like TheGameiswar, I really do not think SQL will fade any time soon. But besides this, what might be true in your part ...


1

I don't know much of T-SQL but I believe that to compare strings you need the logical operator AND. Try this: UPDATE StaffCopy SET StaffCopy.LastName=IIF(([LastName]='Avon' AND [FirstName]='Sarah'), 'Tyne', [LastName]);


2

You can assign any numbers with a derived table. generate_series() is instrumental for a simple solution. With a table like this: CREATE TABLE things_to_do AS SELECT * FROM generate_series (1,6) AS thing_id; A simple solution for the given example SELECT * FROM things_to_do JOIN ( -- derived table with prepared timestamps SELECT row_number() OVER ...


0

Relatively green Firebird n00b here w/a fair PostgreSQL background, although specialized GIS work focused mostly to DB design and the sourcing/cleaning/making spatial multi-source data sets (usually of dubious quality) and then querying for map-able results in answer to specific location riddles. Toolbox is light, therefore, on Admin/Optimization/3rd Party ...


-1

Way 1 : I would suggest to have case embedded in your select. Way 2 : Have a function which does all the maths and call that.


1

Yes, something like the below. SELECT * FROM DataTable WHERE (@SearchField = 'CreateDate' AND CreateDate BETWEEN @StartDate AND @EndDate) OR (@SearchField = 'ModifyDate' AND ModifyDate BETWEEN @StartDate AND @EndDate) Or, to use the example WHERE clauses you specified, how about the below? SELECT * FROM DataTable WHERE ( @ReportType = 1 ...


0

It depends but more than likely yes (with some potential performance issues due to different query plans being cached). Can you give a little more detail on what exactly the type of values are entered in the parameter? Are the 3 values all going to be dates, is the parameter itself a string value that you need to convert to the appropriate type, and is ...


0

According to the question(though i dont know what is written in the book that you are following) it seem that you want to create new user and you dont have privilege to execute that statement. Keep in mind that database users and APEX users different. In order to do that on login page: In Username, enter admin. In Password, enter the Oracle Application ...


1

Query #1: You are missing indexes on sessions.ip and auth.session. The latter is really important because now during the join for each row in sessions (~2M) MySQL has to check all ~4M rows in the auth table instead of picking the one or few directly using the index. That means the query is maybe 1,000,000 times slower than it needs to be. You can make the ...


3

mysql error 1064 The error that gets thrown if the syntax is wrong. You did not clearly state what you are doing. If you are changing a column ALTER TABLE table_name MODIFY COLUMN column_name datatype If you are trying to change column definition ALTER TABLE table_name CHANGE old_column_name new_column_name data_type(size) If you are adding a ...


-2

Use this syntax: Alter table tablename CHANGE [COLUMN] old_col_name new_col_name column_definition


0

why you are calculating max of runID, could you please elaborate this and also NOT IN is very heavy for the performace of a query so please try to remove it. Moreover i see you are using same table in subquery, so why dont you use self join here instead of it.


2

You can write your query like this: ALTER TABLE database_name.sample62 CHANGE old_column_name new_column_name INT(11) DEFAULT 0 NULL;


2

I believe MySQL requires the ALTER TABLE ... CHANGE ... statement to restate the column definition (datatype, default value, column constraints &c.) even if it's only the column name being changed.


2

You could just use %_99_% instead of %99%. % means 0 ore more characters _ means exactly 1 character So %_99_% means at least 1 character before and after 99, and anything before and after. SQL> select * from t1; C1 -------------------- 1991 1199 9911 SQL> select * from t1 where c1 like '%_99_%'; C1 -------------------- 1991


0

Please see below for my suggested answer. The answer below was written for and tested on Microsoft SQL Server but it appears that you are using MySQL so you may have to translate it a bit. It's not the best answer and I hate using a cursor, but it's the only thing I can think of for this specific problem. DECLARE @Table1 TABLE ( Heirarchy ...


0

Ok, it take me some time but I think it works well: select clickcume nr_clicks, avg(view_b) avg_view_before from( select userid, clickcume, max(viewcume) view_b from( select a.*, b.time_v, b.viewcume from ( select userid, time as time_c, count(*) over (partition by userid order by time) as clickcume from views) a join ( select userid, ...


4

rn cannot be referenced in WHERE because it's defined in the SELECT clause. Try using a subquery instead: SELECT * FROM ( select payee_id,start_dt, row_number() over(partition by payee_id order by start_dt) as rn from xxx ) AS sub where rn = 1; For more reading, google "logical query processing order".


1

You can use CROSS APPLY to get the MAX(date) for each Venue and then ORDER BY it: SELECT Event_number, Venue, [Date], Attendance, rn FROM dbo.SampleTable s CROSS APPLY ( SELECT md = MAX([Date]) FROM dbo.SampleTable WHERE s.Venue = Venue ) ca ORDER BY ca.md DESC, Venue, s.rn SQL Fiddle By the way, you can insert into the sample table with 1 ...


2

You can use the the max() with an over() clause partitioned by Venue to get the max date for each venue and then use that as the first column in the order by clause. select T.Event_number, T.Venue, T.Date, T.Attendance, T.rn from ( select S.Event_number, S.Venue, S.Date, S.Attendance, ...


0

Perhaps changing your JOIN condition slightly might help if you want the value B_GRADUATION_TERM that doesn't match TERM_CODE without creating a Cartesian join : From: grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE; To: (grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE or grad.B_GRADUATION_TERM is NULL); As shown completely here: SELECT ...


0

As a rule of thumb conditions on the Outer table are usually placed in WHERE, but on the Inner table in ON: SELECT DISTINCT student.Student_ID as 'Student_ID', case when class.class_id is null then 'No Class ATM' else class.class_id end as 'Class_ID', case when Attendance.Attendance_Status is null then 'Not Present' else ...


0

A transaction like Kenneth Fisher answered And look at optimizing Why are you updating after the insert? Why not just insert a hard coded TargetAmountSetTable.isModified of 1 in the insert? Another option is to output the whole lot of joins once to a #processTable


2

In general what you want here is an explicit transaction around your code. BEGIN TRANSACTION ... your code .... COMMIT However there are down sides to concurrency here as well. While your transaction is open any locks needed to perform the tasks will be held until the end of the transaction. That means that the next set of code that needs to run will ...


3

According to the My Oracle Support, ORA-01792 is caused due to an unpublished bug. Oracle suggest to alter the query and/or view definitions to avoid the error. However in cases where the SQL cannot be adjusted then the checking can be disabled by: SQL> alter system set "_fix_control"='17376322:OFF'; Alternatively an interim patch for this fix (patch ...


0

Tricky around the oldest driver but I'll have a go: SELECT name, birthday FROM driver WHERE name in (SELECT name FROM driver where driver.address = 'haifa') AND d.id in (SELECT d.id FROM event_list) AND birthday = (SELECT DISTINCT MAX(birthday) FROM driver WHERE name IN (SELECT name FROM driver WHERE driver.address = ...


0

Try this code: SELECT TOP 1 name, birthday FROM driver INNER JOIN event_list ON driver.d_id=event_list.d_id WHERE driver.address='haifa' ORDER BY birthday;



Top 50 recent answers are included