Tag Info

New answers tagged

2

You should always use the schema prefix, avoid using sketchy BETWEEN for date range queries, and try not to force regional and ambiguous date formats like d/m/y or m/d/y. Your main problem, though, is that you're intentionally ordering by a string representation of a date, with day first. This means that all of the firsts (regardless of month or year) will ...


2

Like you already found out, there is no (predefined) order in a result set unless you specify it in the ORDER BY clause. Your only option would be, to rename the table and create a view on it. RENAME TABLE your_table TO another_table_name; CREATE VIEW your_table AS SELECT * FROM another_table_name ORDER BY id DESC; Test of course heavily if you run into ...


3

You could do it using UDV: #MIN sync_time of user SET @StartDate=(SELECT MIN(sync_time) FROM test._sync_history WHERE object_name='user' LIMIT 0,1); #MAX sync_time of user SET @EndDate=(SELECT MAX(sync_time) FROM test._sync_history WHERE object_name='user' LIMIT 0,1); #Query: SELECT * FROM test.user WHERE lastmodifieddate BETWEEN @StartDate AND @EndDate; ...


-1

From what I gather I would do: SELECT * FROM user WHERE lastmodifieddate IN ( SELECT min(sync_time), max(sync_time) FROM _sync_history WHERE object_name= "user" );


4

You need to find both times, one using what you have (LIMIT 1) and the second using LIMIT 1 OFFSET 1. I'd also use ORDER BY ... DESC, not ASC. Your wording suggests you want the rows between the last 2 appearnces of 'user': SELECT * FROM user WHERE lastmodifieddate > (SELECT sync_time FROM _sync_history WHERE object_name = ...


4

You need two changes: a major one: Using an non-aggregated column in a GROUP BY query will yield unpredictable result - it's a pity that default setting s in MySQL allow this type of query, and good that it is corrected in 5.7 version. The problem with your query is that an email address can appear in many rows - with many different created_at values - and ...


0

Yes you can run those queries: SELECT email_address FROM footable GROUP BY email_address ORDER BY created_at ASC LIMIT 0,1000; SELECT DISTINCT email_address FROM footable ORDER BY created_at ASC LIMIT 0,1000;


1

Try how good this works for you Converted left join to one inner join for table Two for both t_id's. And on computing min, using case statement to split to two columns based on the value t_id. What we save here is scanning the table two time and an inner join compared to left join SELECT o.p_id, o.k_id, MIN(case when t.t_id = 1 then t.pos else ...


-4

In Oracle select a.*, b.commission, b.date from sales_staff a, comission b where a.id = b.id;


6

If you're running on MySQL: SELECT c.ID, ss.LASTNAME, ss.FIRSTNAME, ss.STREET, ss.CITY, ss.STATE, ss.ZIP, GROUP_CONCAT(c.COMMISSION ORDER BY c.DATE SEPARATOR '\n') AS COMMISSION, GROUP_CONCAT(c.DATE ORDER BY c.DATE SEPARATOR '\n') AS DATE FROM db1.Sales_Staff AS ss JOIN db1.Commission AS c ON (c.ID=ss.ID) ORDER BY ...


2

It makes a lot of sense to me. The MySQL Query Optimizer looks over the WHERE, GROUP BY and ORDER BY clauses. Look at the first query select sts_in from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6' order by sts_in desc limit 1; Which index in sta_session has the most columns mentioned in the WHERE, GROUP BY, and ...


1

I'll venture a guess at what you're looking for. I've made the assumption that the table is called test, the column which contains the entries for FIREARMS, MONEY, etc. is called category, and of course you have your other columns. So, with a simplified table and some data defined as CREATE TABLE test ( id integer, person varchar(30), category ...


2

There are several ways to do it. However, this may work for you: select myTable.* from myTable order by (case myCol when 'FIREARMS' then '' else myCol end);


1

Learn about composite indexes; use them where appropriate. For example: ON cpr.product_id = cp.id AND cpr.branch_id = 3 begs for either of these: INDEX(product_id, branch_id) INDEX(branch_id, product_id) Indexing flags (such as active) rarely useful. Don't use LEFT unless the "right" table has optional data. What do you expect the ORDER BY to do if ...



Top 50 recent answers are included