Tag Info

New answers tagged

0

Not sure what was happening in the past with my queries but this query worked: SELECT date,time,logs.src,src_ipinfo.country, dst,proto,sentbyte,rcvdbyte, COUNT(logs.src) hits FROM `logs`, src_ipinfo WHERE dstport = '60595' AND logs.src = src_ipinfo.src GROUP BY logs.src, logs.dst ORDER BY hits DESC; And the result was: mysql> SELECT ...


0

SELECT * FROM wp_posts WHERE id IN (SELECT comment_post_id FROM wp_comments); or SELECT A.* FROM wp_posts A INNER JOIN (SELECT comment_post_id FROM wp_comments) B ON A.id = B.comment_post_id;


1

I assume you mean you want to find the "first" row with same Ticker_ID and same or bigger DateTime, first when ordered by DateTime ascending. One way to do this, assuming that the price table has a unique constraint on (Ticker_Ticker_ID, Price_DateTime): UPDATE flaggedcomment AS fc INNER JOIN price AS p ON p.Ticker_Ticker_ID = ...


4

There might be hardware issues, too - how should we know? But there are certainly issues with the query. First of all, remove DISTINCT from your VIEW definition. It's doing nothing at all (but complicating and slowing things down). Related answer on SO with explanation: PostgreSQL - Slow query joining on a VIEW Arriving at this (cleaned up) query: ...


1

I'm guessing your << is not utilizing an index, because your primary key would create a btree index, and what you need to make that particular operation indexable is a gist index. (use explain analyze to confirm). if that is the case that index not being used, Try doing something like CREATE INDEX idx_network_info_network_gist ON ...


1

I think the best option is to use nexval on the sequence of the new table. with x as ( select nextval('new_table_id_seq') as new_id, A.x, B.y from A JOIN B ON A.w = B.z ), y as ( insert into new_table (id, x,y) select new_id, x,y from x ) select new_id, x,y from x;


1

Perhaps there's a better alternative but I can only think of joining back to the 2 tables. This assumes that new_table has a unique constraint on (x,y) and that these columns are not nullable: with ins (id, x, y) as ( insert into new_table (x, y) select A.x, B.y from A join B on A.w = B.z returning id, x, y ) -- insert into another_table (id, z) ...


0

You could GROUP BY before you do any joining... Which means, reverse your query. This is your OLD query SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person JOIN license ON license.person_id = person.id JOIN car ON car.owner_id = person.id WHERE person.name = 'Charles Bannerman' GROUP BY person.id; This is your NEW query WITH ...


0

SELECT person.name, person.id, license.expiry_date, COUNT(car) OVER (Partition by Person.Id) FROM person JOIN license ON license.person_id = person.id JOIN car ON car.owner_id = person.id WHERE person.name = 'Charles Bannerman'; You can use OVER (partion by <grouping column(s)>). This will aggregate the data with out having to ...


1

seharusnya seperti ini(supposed to be like this): SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person JOIN license ON license.person_id = person.id JOIN car ON car.owner_id = person.id WHERE person.name = 'Charles Bannerman' GROUP BY person.name, person.id, license.expiry_date, car.car;


1

Try: select ac.Account_Name, ac.Account_ID, count(t.ID) as 'Total Tickets' from accounts ac join tickets t on ac.Account_ID = t.Account_ID group by ac.account_ID, ac.account_name I think the only error in your query is that you aliased "tickets" as "ticket", but your query is also more complex than it needs to be.


0

Since you said you only want fields from table A in your comment, you just need to test existence like Evaldas said. I copied your original query from the hibernate post and rewrote it here. I hope it helps! select this_.id as id3_, this_.form_id as form2_20_3_, this_.access_rule_id as access3_20_3_, this_.state_id as state4_20_3_, ...


7

Query 1 will show you All records from student with a Class value of 7, and then any related Library_Book records if they exist. Query 2 will show you all records from student, and related Library_Book records if they exist and if the matching row in student has a Class of 7. Query 3 will show you All records from student with a Class value of 7, and ...


1

Also consider the first answer. Query This does what your current query currently does, just simpler and faster: SELECT p.id, p.note, p.photo, p."createdAt", u.id AS "user.id", u.name AS "user.name", h.id AS "hashtags.id", h.count AS "hashtags.count", h.name AS "hashtags.name", h."createdAt" AS ...


0

Also consider the alternative answer. Query This does what your description says, not what your query currently does (see comment): Select Posts with a photo and a Hashtag that has the name 'dead' .. limited to the latest 10 qualifying posts: SELECT p.id, p.note, p.photo, p."createdAt", u.id AS "user.id", u.name AS ...


1

Command 1 is the SQL-89 standard for joining between tables. Command 2 is the correct method of writing JOINs as per the ANSI-92 SQL standard (yes, that is 92 as in 1992). Some 23 years later and people still don't want to conform to ANSI-join standards. Theoretically, there is no difference between the two in terms of performance, it's apparently just a ...


4

Well, condition2 seems to cover condition1 and condition3 too. What you probably need would be something like: ;WITH CTE AS ( SELECT *, N = RANK() OVER(PARTITION BY cpt, cpt4 ORDER BY eff_date DESC) FROM dbo.YourTable ) SELECT * FROM CTE WHERE N = 1; This should cover everyone of your conditions.


0

Here's a stab at this: see fiddle at http://sqlfiddle.com/#!9/1fc60/1. My algorithm in general to take all the start/end values for all clients, sort those numbers and make each adjacent pair of values into an interval. For n clients, you have 2n-1 intervals (in worst case). Then, evaluate each of those intervals to see how many clients' IP ranges they ...


1

What you see is a "proxy cross join". Aggregate first, then join: SELECT m.id, COALESCE(l1.ct, 0) + COALESCE(l2.ct, 0) AS total_ct FROM main m LEFT JOIN ( SELECT main_id, count(*) AS ct FROM link1 GROUP BY main_id ) l1 ON l1.main_id = m.id LEFT JOIN ( SELECT main_id, count(*) AS ct FROM link2 GROUP BY main_id ) l2 ON ...


1

I'll attempt to answer it myself. Consider a LEFT JOIN between main & link1. The output would be main.id link1.main_id 1 1 1 1 2 2 3 NULL 4 NULL Now do a LEFT JOIN of the above table with link2, output would be: main.id link1.main_id link2.main_id 1 1 ...


0

That is not the correct syntax for an UPDATE+JOIN. Try the following (assuming it is what you want, updating the table customer): UPDATE customer b JOIN received_customer n ON b.phonenumber = n.phonenumber SET b.followUpStatus= 'y' ;



Top 50 recent answers are included